How to add NON_NULLABLE field in ESRI to an existing table?

Question

Is there any way to add NON_NULLABLE field to an existing table with existing records? Just to make it a bit more fun the field must have Field domain parameter too. And I am fine to start with having all records set to first (default) value in domain.
In documentation to AddField_management there is nothing about that it can't be used on table with existing records. But sneakily in documentation to AlterField_management it is stated that 'This option is only applicable if the input table is empty (does not contain records).'. Which I guess is the reason why my adding ends up with 'something unknown happened' error.
So I guess in ESRI world nobody ever needed to add a non-nullable field, right? Unfortunately I can't create new table as we are talking about table with all the ESRI specials including versioning, archiving, and editor tracking which I can't drop just for fun.
I am thinking about adding field using SQL, to make it more fun I will have to do the same for archive table, and to make it even more fun, use AssignDomainToField later back in ESRI. Sounds like a perfect safe way for something as simple as add non-nullable column, right? Am I missing something?

Miro · Answer

After lots of more testing I can confirm there is no way to add NON_NULLABLE column in ESRI software to already populated table. Or change column to NON_NULLABLE in ESRI software. Both is easily achievable with other software or directly through SQL on database level though. This approach is not recommended as getting around ESRI software to do changes directly on table managed by ESRI geodatabase is asking for troubles. Use it only if you run out of alternatives like unfortunately I had to:
Create new non-nullable column directly in database
SQL Server example:
ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

PostgreSQL example:
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT 'foo';

In my case I have archiving on so I had to do the same for archive table. Also I needed to apply existing domain on this field from ESRI geodatabase. Fortunately at least this is easily achievable through user interface or using arcpy.AssignDomainToField_management. Also after populating field with requiered values, you will likely want to remove the default value constraint (see linked examples if you are unsure).
I also tried add column as nullable first, and later on database level changed it to not allow nulls, as I felt it is more safe approach. This is possible to do, and on database level you won't be able to add new record or update this field with null. But unfortunately ESRI software does not pick up this change in my case, and in design view it still presents field as nullable.

How to add NON_NULLABLE field in ESRI to an existing table?

One Answer

Add your own answers!

Ask a Question