6

Tables that are created in Oracle using ArcGIS Desktop are given an OBJECTID field by default. The OBJECTID field is assigned a unique not-nullable index.

Does the OBJECTID in an Oracle Geodatabase table have a primary key constraint, by default?

I ask, because there can only be one field with a primary key constraint per table. If the OBJECTID does not have a primary key constraint by default, I plan to add a primary key constraint to a different field -- a field called ID. I will use the ID field in conjunction with a foreign key to enforce referential integrity at the database level. More info here.


Note: A primary key constraint is not to be confused with a unique not-nullable index, a candidate key or a so-called relationship class "primary key".

  • The problem lies in the question. There are many columns which would qualify as a primary key which are not a PRIMARY KEY. Esri supports a dozen different databases, so they're not likely to get tangled in semantics -- if enabling a PRIMARY KEY hurts performance with no functional benefit, they don't enable it. It is not politic to criticize vendor implementations, so they don't go into details about why certain database features aren't used. The real answer is "No, but it doesn't matter." You will not find an authoritative source for this. – Vince Mar 13 '17 at 11:47
  • You need to define your meaning of "primary key". – Vince Mar 13 '17 at 13:36
  • @Vince I would argue that it does matter. I can use a primary key constraint and foreign keys to enforce referential integrity at the database level. This is useful for environments where non-ArcObjects applications edit the data. But in contrast, I can't force referential integrity with a unique, not-nullable index. So to me, it matters. – Wilson Mar 15 '17 at 2:37
  • You should not use the registered rowid column for any purpose. It exists solely for ArcGIS' use. If you add your own primary key, you need to maintain it, which will be nearly impossible in a versioned geodatabase. You can basically have an enterprise geodatabase or you can roll your own and use Query Layers. – Vince Mar 15 '17 at 2:38
  • @Vince I mean, ESRI says the same thing about other database-level things like unique indexes. Depending on which ESRI page your read, they say that unique indexes will fail in a versioned geodatabase. But I've found this to be false. Unique indexes have worked fine for me for years, in an option to move edits to base versioned environment. Based on what I know about the option to move edits to base environment, I don't understand why adding my own primary key constraint won't work. Is it so different than the unique indexes I've been using for years? – Wilson Mar 15 '17 at 17:40
5

By default, yes, it normally is unless someone overrode that and designed the table differently.

Is this an ArcSDE database? There may also be GlobalID fields that act as primary keys across multiple databases with relationship tables, so that may add some complications.

You are correct; there can only be one primary key. However, with the right permissions, you should be able to edit which field is acting as a primary key if you needed to.

  • Thank you for your answer. Do you have a source that says the OBJECTID is the primary key? Or when you query using SQL to determine the keys of a table, does the OBJECTID show up as the PK? I'm unable to test this at the moment. – Wilson Dec 27 '16 at 3:03
  • from ESRI: desktop.arcgis.com/en/arcmap/10.3/manage-data/relationships/… "When deciding on a primary key field, one option is to use the row ID field, commonly referred to as the ObjectID field. The ObjectID field is automatically added by ArcGIS when you create a feature class or table or register an ArcSDE layer or table. This field guarantees a unique ID for each record. It is maintained by ArcGIS, and you can't modify it." At the end of the day, the question is if you want to follow ESRI architecture or do your own with Oracle. – GIS87 Dec 27 '16 at 20:45
  • That ESRI page seems pretty misleading. I think they're calling a field in the relationship class a "primary key", even though that field in the database isn't actually a primary key. "Unlike a true primary key, the values in the primary key field in a relationship are not required to be unique for every object." I don't believe the OBJECTID is a true primary key (does not have a primary key constraint). See my answer below. – Wilson Jan 2 '17 at 16:19
  • Maybe ESRI would have been better of calling the so-called relationship class "primary key" & "foreign key" something like parent ID and child ID. – Wilson Jan 2 '17 at 16:47
  • I don't know what you used to test this, but from what you were showing you are correct; the ObjectID is not the primary key as you showed in this specific case. However, if you are using an ESRI geodatabase created through ArcCatalog then by default ObjectID would be the primary key (a record-unique field that is automatically assigned and you cannot edit it). My previous work with Oracle geodatabases had the ObjectID field as the primary key with a GUID as an indexed relationship in their tables. However, like I said, and you just proved, this is not always the case. – GIS87 Jan 3 '17 at 13:31
4

I think the answer is no. The OBJECTID in an Oracle geodatabase table does not have a primary key constraint , by default.

When I query an Oracle geodatabase table to return the column that has a primary key constraint, the OBJECTID is not returned/flagged:

SELECT 
    cols.table_name, 
    cols.column_name, 
    cols.position, 
    cons.status, 
    cons.owner
FROM 
    all_constraints cons, 
    all_cons_columns cols
WHERE 
    cols.table_name = 'ROAD'
    AND cons.constraint_type = 'P'
    AND cons.constraint_name = cols.constraint_name
    AND cons.owner = cols.owner

The result set is empty:

+------------+-------------+----------+--------+-------+
| TABLE_NAME | COLUMN_NAME | POSITION | STATUS | OWNER |
+------------+-------------+----------+--------+-------+
|            |             |          |        |       |
+------------+-------------+----------+--------+-------+

In contrast, I've created a dummy table with a primary key constraint:

CREATE TABLE A_TEST
(
 P_ID int NOT NULL PRIMARY KEY
)

When I run the query on it, the field with the primary key is returned, as expected:

+------------+-------------+----------+---------+-------+
| TABLE_NAME | COLUMN_NAME | POSITION | STATUS  | OWNER |
+------------+-------------+----------+---------+-------+
| A_TEST     | P_ID        |        1 | ENABLED | ENG   |
+------------+-------------+----------+---------+-------+

As far as I can tell, this proves that the OBJECTID in an Oracle geodatabase table does not have a primary key constraint. Just because the OBJECTID is similar to a primary key (has a unique not-nullable index) it doesn't mean that it actually has a primary key constraint.

2

No, OBJECTID does not have a database level Primary Key constraint.

OBJECTID is managed directly by ArcGIS, not by the underlying database. Esri Geodatabases do not have database managed keys, and it would be unwise to apply your own due to the ArcGIS system managing all geodatabase relationships itself.

See my answer to your other question Can primary keys and foreign keys be used in enterprise geodatabases?

Please also note that the field name does not always have to be OBJECTID - it is possible to have a different field name, and these field names are managed through a separate table in your geodatabase. From What is an ObjectID?:

  • An ArcGIS-maintained ObjectID field is automatically added to any table created using ArcGIS.
  • If you register a table with the geodatabase that does not have a qualifying field, the geodatabase adds another field to the table that meets the requirements of an ObjectID and names it OBJECTID. If your table already contains a column that is named OBJECTID, the geodatabase adds a column named OBJECTID_1.
  • If you register a table with the geodatabase and the table does contain a qualifying field (integer, not null), the existing column can be used as the ObjectID.

Also see What's the best way to get the OBJECTID name?

In my local geodatabase I have a table called SDE_table_registry which has a column rowid_column listing the name of the OBJECTID equivalent field in each of my tables (although I only have 2 feature classes in my geodatabase and they both have the standard OBJECTID field name).

enter image description here

1
+100

My understanding is that both Primary Key and Foreign Key are merely database constraints. So you can ALTER TABLE to define/add/drop these constraints.

The PRIMARY KEY constraint is the same as UNIQUE constraint plus NOT NULL constraint (i.e. saying a set of columns is a candidate key). Among the potential candidate keys, you can choose one and at most one to be the primary. Since OBJECTID alone is unique and not null, it is a true candidate key.

As to whether OBJECTID is chosen along the way by default, your experiment suggests that it's not. But I don't think that's fundamental. You can add the PRIMARY KEY constraint yourself or change it by dropping/adding the constraint. The software vendor may or may not choose to do it in their next version (if any).

I find the linked ESRI page confusing too, as they call something "Unlike a true primary key" a primary key. On the other hand, I think the problem (with uniqueness) primarily occurs when splitting an existing feature. But maintaining referential integrity when a unique ID splits is complicated anyways. I can't think of a clean way to handle this automatically except maybe to delete records cascadingly.

My two cents.

  • Do I understand correctly, that another difference between a primary key constraint and a unique, not-nullable index is that a field with a primary key constraint can be used for referential integrity, and an index cannot? – Wilson Mar 15 '17 at 2:19
  • 1
    I don't know if index is directly relevant. According to docs.oracle.com/cd/B28359_01/server.111/b28286/…: A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration and the referenced key in a FK constraint can be the primary key, but doesn't have to be. If you identify only the parent table or view and omit the column name, then the foreign key automatically references the primary key of the parent table or view – tinlyx Mar 15 '17 at 3:05
  • 1
    "You can add the PRIMARY KEY constraint yourself or change it" - Yes, you can but changes to the schema of a geodatabase table/feature class at the database level, particularly to ArcGIS system fields such as OBJECTID is likely to be unsupported by the vendor – Midavalo Mar 16 '17 at 20:08
  • @Midavalo You make a fair point. However, I find it useful to challenge anything ESRI has to say about databases, and instead look to traditional relational database theory. Relational database theory seems more established than ESRI and object-relational geodatabases. And I find the GIS community often ignores relational database design principles. So yeah, I'm going to try some unsupported things. Some will fail, and others won't. And I'll learn a bunch, at the very least. – Wilson Mar 16 '17 at 20:39
  • @tinlyx Your comment ...the referenced key in a FK constraint can be the primary key, but doesn't have to be was extremely helpful -- thanks. I had mixed this up. I thought foreign keys could only reference primary keys. Obviously I was wrong. Foreign Key: Designates a column as the foreign key and establishes a relationship between the foreign key and a primary **or unique key**, called the referenced key. docs.oracle.com/cd/E11882_01/server.112/e40540/… – Wilson Mar 16 '17 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.