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?
Best Answer
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