nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Encontre mais publicações marcadas com

Enterprise geodatabase

Databases

Geodatabase Administration

Accepted answers

All comments

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

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.

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

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.

JanDlouhy

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

AdminNCCMA

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

VinceAngelo

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

AndyEgleton

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

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.