Disallow Null Value field in existing data

[AlexP_]

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

[DanPatterson]

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

[JohannesLindner]

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...

[AlexP_]

@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.

[JohannesLindner]

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 !!!

[VinceAngelo]

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

[AlexP_]

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

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

[VinceAngelo]

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_]

@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.

[VinceAngelo]

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