6

I've been brushing up on relational database design theory, and I keep coming across primary keys (PKs) and foreign keys (FKs).

PKs and FKs are said to be pillars of relational database design and are used to enforce referential integrity at the database level. Yet, as far as I can tell, they're not used in enterprise geodatabase design (they don't seem to be mentioned in any of the documentation, training, or toolsets).

Instead, relationship classes are used. Relationship classes seem to work well enough for enforcing referential integrity in ArcGIS Desktop (they can be used in every kind of geodatabase, not just enterprise geodatabases).

But in my case, I want to enforce integrity at the database level. I don't only use ArcGIS Desktop to edit data; I have other applications that are used to edit my versioned Oracle geodatabase using SQL/versioned views. Instead of having referential integrity at the ArcGIS application level (relationship classes), it would be benefical to enforce referential integrity at the database level, for use in other SQL-based applications.

Can I use PKs and FKs in my versioned Oracle geodatabase? Or would PKs and FKs cause problems with versioning?

  • 4
    This reads more like a rant than a question. The only definitive answer would come from Esri, but they don't post here. Have you tried asking Tech Support? I would guess they would say that primary keys are fraught with complications in a versioned environment, and they hurt performance. There is only one kind of versioning, so you are making a distinction that doesn't exist in reality. – Vince Jan 10 '17 at 4:34
  • @Vince Fair points (question revised). And I can ask support. I was hoping that someone on SE might say something, based on experience, like: "ESRI/support dont like PKs and FKs because of x, but if you do y and z, they'll work out ok". – Wilson Jan 11 '17 at 12:45
  • 2
    I can't think of a way they'd work out okay, but I can see lots of ways they'd cause outright failure. – Vince Jan 11 '17 at 14:11
2

Problem is not in relation with pk or fk but with Unique IDs in the SDE environment.

It's recommended to use in all tables (featureclass) GlobalIDs and others GUID fields.

A relationship class facilitates one-to-one, one-to-many, or many-to-many relationships, using GUIDS (or other fields, but GUIDS are recommended). GUIDS and relationship classes are SDE's answer to the Primary Key/Foreign Key concept.

You can look at Maintaing Referential Integrity in ArcSDE

  • I don't think I understand why Unique IDs are the problem. OBJECTIDs are unique. And I've been using unique indexes on other fields in versioned Oracle geodatabase (ArcSDE) tables for years (versioned with the option to move edits to base) without issue. It's my guess that primary key constraints and foreign keys could be used in a geodatabase table that is versioned with the option to move edits to base. – Wilson Mar 15 '17 at 1:37
  • @Wilson in fact you are right and GlobalIDs and GUID are use used to maintain uniqueness of records in one-way and two-way geodatabase replicas and nonversioned data used in offline maps. But that is just a recomendation... – GeoStoneMarten Mar 15 '17 at 8:39
2
+100

Warning: Any modifications to the underlying tables in an enterprise geodatabase are likely to be unsupported and should be tested and re-tested (in a non-production test environment) before you even think about trying it in your production database.

Also note: You will be more likely to get a definitive answer to your question by asking Esri directly rather than here, particularly as what may work in one scenario may not actually work for you (or vice-versa).

Now that that's out of the way, this is my simplistic understanding of how Key fields work in an Esri Geodatabse.

ArcGIS manages its own relationships in a geodatabase using primarily the OBJECTID field, and occasionally the GLOBALID field, as well as other Key fields in related system tables (SDE_ and GDB_). There are often many tables involved when you look at geodatabase tables/feature classes that may appear to be only a single item when viewed through ArcGIS.

Because the ArcGIS system manages the table relationships there is no need for database specific Primary/Foreign keys. The OBJECTID should not be used as a unique identifier because it can change (if you rebuild a feature class, or copy features) and cannot be modified in any way by the user (ie if trying to re-match IDs if they have changed). It is also possible for a previously used OBJECTID to be reused

The use of Oracle dbms_pipes can result in previously returned or unused ObjectIDs being assigned when any client, running in the same Oracle instance, subsequently requests ObjectID(s). At this point, gaps in the ObjectIDs are 'filled in'.

To use Primary and Foreign keys in ArcGIS it is recommended to use GLOBALID and GUID fields:

Developers can use them in relationships or in any application requiring globally unique identifiers. In a relationship, if a Global ID field is the origin key, a GUID field must be the destination key.

So in answer to your question, you can use GLOBALID as a "Primary" key and a GUID type field as a "Foreign" key within the ArcGIS managed environment, but I would recommend against setting these explicitly in the database directly.

See:

2

There is no such thing as a geodatabase. I'll answer in the abstract. That's just marketing wank, there is a database and some of them support spatial types. They can be implemented with all kinds of models: inheritance, relational, hierarchical (ldap, snmp, etc.) And, they can have all different models of concurrency (MVCC, and eventual consistency, etc).

If they support Primary Keys, and Foreign Keys: you know they're of the relational model. You can relate without keys, but keys ensure integrity across the relationship.

I use PostGIS exclusively. No marketing wank: it's PostgreSQL with an extension, foreign keys and primary keys are absolutely essential.

My assumption is if you're asking these kinds of questions, you don't need ESRI. You're right in your answers: their docs are vague because their users rely on them to deliver a solution that's acceptable. If you're going to engineer your own solution, understanding RDBMS is vital.

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.