Best practice to set a table's PRIMARY KEY in a MS SQL Enterprise Geodatabase

TylerT — Fri, 28 Jun 2024 20:30:37 GMT

Using geoaccessor method to_table() and to_featureclass()

do not set a PRIMARY KEY (PK) in the enterprise geodatabase. A PK is required to reflect tables as orm declarative tables with SQLAlchemy's automap_base. This is why I want to set a PK.

Is there any risk to the geodatabase inner workings to set a PK by passing the following SQL:

ALTER TABLE {tbl} ALTER COLUMN {pk} {dtype} NOT NULL ALTER TABLE {tbl} ADD PRIMARY KEY({pk})

This SQL runs without error, and following the PK set, sqlalchemy is much happier. I'm curious if there are any unintended consequences to setting PK with third party tools such as SQL (i.e. outside of ArcGIS API).

If this is NOT recommended, does ArcGIS Pro or Arcpy have a tool to set PK in the EGDB (not to be confused with relationship class PKs), or do you have any other suggestions?

Thank you,

Tyler

Re: Best practice to set a table's PRIMARY KEY in a MS SQL Enterprise Geodatabase

AndreaB_ — Mon, 12 May 2025 21:47:19 GMT

Darn! I was wondering about this part "does ArcGIS Pro or Arcpy have a tool to set PK in the EGDB"? I noticed that I can change the configuration keywords to make the index clustered on the OBJECTID but I would love to set it as the PK as well.

Any ideas?

Thanks,

topic Best practice to set a table's PRIMARY KEY in a MS SQL Enterprise Geodatabase in Python Questions

Best practice to set a table's PRIMARY KEY in a MS SQL Enterprise Geodatabase

Re: Best practice to set a table's PRIMARY KEY in a MS SQL Enterprise Geodatabase