HOW TO
Maintain DBMS referential integrity with ArcGIS and non-versioned data
Summary
ArcGIS 9.2 provides the ability to edit non-versioned data with ArcMap. Non-versioned editing utilizes a DBMS transaction when editing feature classes and tables. During non-versioned editing, the transaction's duration begins with the first edit operation after start editing and continues until Stop Editing or Save edits. All edits within the transaction are either committed or rolled back based upon the user's decision to save/commit or discard the edits/rollback.
With non-versioned data, one can implement DBMS referential integrity between object classes and model the referential integrity with geodatabase relationships.
It is important to remember when using geodatabase relationships with DBMS referential integrity, the workflow must account for the referential integrity behavior.
Editing a class participating in a simple relationship class results in additional edits. For example, deleting an object in the origin object class results in the geodatabase setting the foreign key on the destination class to NULL. In the case of composite relationship classes, the deletion of the origin class object results in the deletion of the related destination class object.
The order in which these changes are written or flushed to the DBMS is dependant on the order of the objectclasses in the geodatabase's object pool.
Note:
The object pool is a geodatabase cache maintaining information about object classes which have been opened by the session.
The geodatabase maintains the object pool by converting the names of the objectclass to hash values. The order of the objects in the pool is based on the hash value. One has no control over the assignment of the hash value and the order of the objects in the pool.
When the application calls store or at the termination of an edit operation, the changes in the object pool are flushed to the DBMS. The order that the edits are flushed to the DBMS is dependent on the hash order in the object pool.
The following examples demonstrate the behavior and results when editing a relationship class which also contains referential integrity:
Example: Edits flushed from the object pool in the correct order
Data:
GIS.MAINTENANCE
Referential integrity between STREETS and MAINTENANCE
Geodatabase Composite Relationship (Origin class STREETS, Destination Class MAINTENANCE) -
When an editor deletes a STREET feature two edits occur: the delete of the related MAINTENANCE row and the delete of the street. The fully qualified objectclasses, GIS.STREETS and GIS.MAINTENANCE, have already been converted to a hash value and exist in the object pool, and the edits are flushed to the DBMS in the order they are discovered in the object pool:
· The deletion of the STREETS feature.
Because the edits are flushed in this order, the referential integrity constraint is not violated; the foreign key is deleted prior to the primary key.
Example: Edits flushed from the object pool in the incorrect order
When an editor deletes a STREET feature two edits must occur: the delete of the related MAINTENANCE row and the delete of the street. The fully qualified objectclasses, GIS.STREETS and GIS.MAINTENANCE, have already been converted to a hash value and exist in the object pool, and the edits are flushed to the DBMS in the order they are discovered in the object pool:
· DMBS referential integrity constraint violation is encountered and an underlying DBMS error is returned.
Attempting to delete the STREETS feature first results in a referenial integrity violation because there are corresponding record(s) in the MAINTENANCE table. The referential integrity constraint does not allow any corresponding records in the child table to exist.
In the above examples, the hash value for the objectclasses in the object pool resulted in two different results when the edit operation was completed and the geodatabase attempted to flush the changes to the DBMS.
Because one can not rely on the geodatabase relationship class to apply the changes to the DBMS in the correct order based upon the definition of the referential integrity, solve this problem by employing the correct editing workflow.
Procedure
Employ the proper editing workflow and/or referential integrity constraint to aid the editing process. Use one of the options for editing relationships with referential integrity:- Edit the MAINTENTANCE table before making the edit to the STREETS feature class.
Note:
If the FK value in the MAINTENANCE table is deleted before deleting the record in the STREETS feature class, the referential integrity constraint is not violated. - Add the ON DELETE CASCADE option to the constraint.
Note:
Using this option helps avoid the situation when you delete the STREETS feature encountering the error a dependant foreign key exists. With this option, the DBMS automatically deletes the FK record(s) in the MAINTENANCE table prior to deleting the STREET feature.
Article ID: 000009104
- ArcMap 9 x
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App