SDE - change field from REQUIRED to NON_REQUIRED

[RebeccaStrauch__GISP]

How can I deleted a "REQUIRED" field?
I'm attempting to delete a field that I added thru a python script.  We (tech support and I) were able to track it down to my accidently adding it as "REQUIRED".   (Bummer!)   She's trying to track down the answer on that end, but I thought I would throw it out there to the user group in case anyone knows the trick to do this.  I'm assuming it will be a SQL-Manager hack.

I have tried all the typical ArcCatalog/Map/toolbox methods, and the "replace field" tool in the "X-Ray to Geodatabases" addin with the the "required" box unselected.  The replace-tool did create the temp field, but then still failed with the  "ERROR 001334: Cannot delete required field <fieldname>".

This is my "master" file (which I am doing maintenance one) with all the bells and whistles (versions, edit tracking, archiving, topology-rules, subtypes, domains, etc.) turned on, so I'm trying not to lose all that.  Worst case scenario is I lose the history etc (I'm assuming) and rebuild the subtypes/domains/rules and use the X-Ray tool to clean it all up.

Anyone know any tricks to get rid of the REQUIRED field without destroying my Geodatabase?

ArcSDE (SQL2008) 10.2.1. 

Thanks for any suggestions,
Becky

[RebeccaStrauch__GISP · Accepted solution]

ANSWERED.....I was not able to change the field from REQUIRED to not Required, but with the help of tech support (Thanks Melonie S), I was able to delete the field that was set to REQUIRED by using SQL Manager, which was my original intent.

This does not appear to have caused any issues with my GDB, although it MAY have turned off my versioning. I wanted to document the steps here for others.....but suggest testing on a copy of your database before attempting. That is, USE WITH CAUTION

In SQL Manager:

[INDENT]use yourGDBName
ALTER TABLE [dbo].[FcName] DROP COLUMN [fieldToDelete]
delete from dbo.SDE_column_registry where table_name = FcName AND column_name = fieldToDelete[/INDENT]


dbo = the FC owner, and could be sde or another username
FcName = name of the Feature Class or table that needs modifying

puntuation is as shown, with brackets needed, and no quotes anywhere.
I recommend making sure you dont have any domains or any other topology rules, etc. attached to the field before you delete.

[GregRieck]

Thanks for posting this Rebecca it solved my "required" problem as well. As an added note I was able to execute your SQL statements using IWorkspace.ExecutesSQL.

[VinceAngelo]

The key to altering columns in a registered table is to repeat the procedure
on the adds table and/or archive table. 

Changing a NOT NULL column to NULLS ALLOWED should be quite simple,
and does not require column removal.  It certainly could wreck the geodatabase
metadata, so caution should always be exercised.

- V

[RebeccaStrauch__GISP]

Hi Vince,
In my case, it wasn't to allow NULLS or not, but actually "REQUIRED". I had that as a AddField argument in my arcpy script to update my domains. Most the time I ran it against a new FGDB that I delete/recreate on each update, so it was never an issue. I'm currently updating my MASTER sde database and ran the script, which then added the field to that. I decided I didn't want that field in the master and then couldn't delete it. That is when I looked at my script and saw my error...

arcpy.AddField_management("master, "newField", "SHORT", 0, 0, 2, "anAlias","NULLABLE","REQUIRED")

Good suggestions about looking at the archived tables and metadata for issues after the SQL commands I performed. Especially the metadata. That didn't dawn on my that is could mess with that.

Besides not messing up my GDB topology/tracking/versioning/archiving, I was hoping to be able to keep the history intact, but I'm also wanting to reorder the fields (this is major overhaul of the master files), and I think the only way I can really do what I want is to use the "X-RAY" reorder tool which still wants to recreate the file (or errors out when trying to replace)..which mean the globals will be different and history may not follow forward anyway. But IF I am using the X-Ray option, I found the new file did not have the "REQUIRED" flag on the field in the new file and I could delete if in Catalog then, so I will have to mess with that approach. I'm also thinking of renaming the actual GDB too, and that also seems to be best with a replacement/rebuild. Anyway, just trying to retain as much history as posible, but those are diccussions beyond this thread.

Thanks for your suggestions.