Disallow Null Value field in existing data

411
11
a month ago
AlexP_
by
Occasional Contributor III

Hello,

I have arcigis pro 2.7.2, arcmap 10.7.1, existing sde feature class, sql server.

Is there a way to disallow null values in field setting since it has existing data? Need to have one field that is required entry. It is not register as version. Please advise. Thank you.

Alex

0 Kudos
11 Replies
DanPatterson
MVP Notable Contributor

For file geodatabases, I have only found that disallowing nulls can only be done at the time of field creation.

I am all for not allowing nulls as the default, but once allowed, I haven't found a way to revert it.

An earlier missive on the topic

The solution to <null> in tables - Esri Community


... sort of retired...
JohannesLindner
Occasional Contributor II

If you don't need to work with the tables in question in ArcMap (though you do have ArcMap listed...), you could define Constraint Attribute Rules.

https://pro.arcgis.com/de/pro-app/latest/help/data/geodatabases/overview/an-overview-of-attribute-ru... 

// Triggers: Insert, Update
// blocks the edit if the field NotNullable is empty
if(IsEmpty($feature.NotNullable)) {return False}
return True

// or in one line:
// return !IsEmpty($feature.NotNullable)

You won't be able to open these tables in ArcMap. You can open views containing the data, though. So if you don't do edits in ArcMap, this could be a solution...


Have a great day!
Johannes
AlexP_
by
Occasional Contributor III

@JohannesLindner  Thank you for the information. But it is need to be editing. when an editor entry info, a field needs to be required to entry.

0 Kudos
JohannesLindner
Occasional Contributor II

You can't change the field to disallow Null values as long as there are entries in the table.

  1. Delete all relationship classes your table is part of
  2. Copy your table
  3. Delete all rows from the table
  4. Change your field(s), you can now disallow Null values
  5. Edit the empty field values in the copy (or do it before step 1 in the original)
  6. Copy all rows from the copy to the original
  7. Delete the copy
  8. Recreate all deleted relationship classes

!!! This will change ObjectID and GlobalID values of the table !!!


Have a great day!
Johannes
VinceAngelo
Esri Esteemed Contributor

If the table is not versioned, you can use SQL to alter the column:

ALTER TABLE [{tablename}] ALTER COLUMN [{columnname}] {datatype} NOT NULL

You should then open the table in the Catalog view as the table owner, to make sure the properties are correct, and populate the update to the sde.sde_column_registry table.

- V

0 Kudos
AlexP_
by
Occasional Contributor III

@VinceAngelo Thank you for the information. Please see screenshot. I got an incorrect.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '{'.

AlexP__0-1618514016276.png

 

0 Kudos
VinceAngelo
Esri Esteemed Contributor

The "{tablename}" and "{columnname}" and "{datatype}" need to be replaced with actual values, e.g.,

ALTER TABLE [EDIT_TEST] ALTER COLUMN [TEST] nvarchar({actualwidth}) NOT NULL


(Yes, you need to replace "{actualwidth}" with the defined width of the column).

- V

AlexP_
by
Occasional Contributor III

@VinceAngelo I got another error message. Please advise.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'TEST', table 'EDIT_TEST'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

You can only enforce NOT NULL on a column if the table doesn't have NULL values in that column.

You'll need to fix this first.

- V

0 Kudos