topic Re: Disallow Null Value field in existing data in Geodatabase Questions

Disallow Null Value field in existing data

AlexP_ — Tue, 13 Apr 2021 17:35:24 GMT

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

Re: Disallow Null Value field in existing data

DanPatterson — Tue, 13 Apr 2021 18:50:09 GMT

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

Re: Disallow Null Value field in existing data

JohannesLindner — Wed, 14 Apr 2021 07:52:39 GMT

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

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

Re: Disallow Null Value field in existing data

AlexP_ — Wed, 14 Apr 2021 14:05:30 GMT

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

Re: Disallow Null Value field in existing data

JohannesLindner — Wed, 14 Apr 2021 14:34:29 GMT

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

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

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

Re: Disallow Null Value field in existing data

VinceAngelo — Thu, 15 Apr 2021 19:06:53 GMT

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

Re: Disallow Null Value field in existing data

AlexP_ — Thu, 15 Apr 2021 19:14:06 GMT

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

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

Re: Disallow Null Value field in existing data

VinceAngelo — Thu, 15 Apr 2021 19:33:23 GMT

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

Re: Disallow Null Value field in existing data

AlexP_ — Thu, 15 Apr 2021 19:41:58 GMT

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

Re: Disallow Null Value field in existing data

VinceAngelo — Thu, 15 Apr 2021 19:44:12 GMT

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

Re: Disallow Null Value field in existing data

AlexP_ — Thu, 15 Apr 2021 19:47:19 GMT

@VinceAngelo thank you for clarify. i don't have any data on that column. i recent checked..

Re: Disallow Null Value field in existing data

VinceAngelo — Thu, 15 Apr 2021 20:02:35 GMT

If every row has a NULL value in that column, you certainly don't want to assert a NOT NULL on the column until the table has been updated so that only valid (non-NULL) values are present.

- V

Re: Disallow Null Value field in existing data

Dlouhyj — Tue, 16 Aug 2022 09:00:54 GMT

... and when I have versioned table? Thank You

Re: Disallow Null Value field in existing data

AdminNCCMA — Wed, 31 Aug 2022 03:01:49 GMT

@VinceAngelo Hi Vince, we've found this post quite interesting and have ran your query above and it seemed to work, however when we then check the table in catalog it is still set to allow null values. We then viewed the sde_column_registry table and can't see where the allow/not allow nulls is referenced? In screen shot it is the CreatedBy_temp field that we ran the ALTER TABLE query on.

Re: Disallow Null Value field in existing data

VinceAngelo — Thu, 01 Sep 2022 01:12:35 GMT

Changes made to the base table are not manifested to SDE table metadata immediately. You must first connect as the owner and describe the table (this can only be done as the owner).

You do not want to do this if the table is registered as versioned.

- V

Re: Disallow Null Value field in existing data

AndyEgleton — Fri, 14 Apr 2023 15:19:58 GMT

Hi Vince,

Can you describe the Describe process?

I've tried in the DB and in ArcGIS and it doesn't pick up the change in metadata.

Thanks