DBMS referential integrity with non-versioned editing

Summary

ArcGIS non-versioned editing supports DBMS referential integrity because as edits are performed, the changes are immediately written to the underlying DBMS. If an underlying DBMS integrity constraint is violated, an error message is displayed. Working with data containing referential integrity constraints requires using the correct editing workflow, to prevent DBMS constraint violations.

Note:
When an edit is written to the DBMS, it does not mean the edit is committed. An edit is only committed if the edit session is saved. If the edit session is stopped without saving, the edits in the DBMS are rolled back.

Understanding DBMS referential integrity

• Inserting or updating an attribute in the FK table that does not have a corresponding row with the attribute value in the PK table.
• Attempting to delete rows in the PK table that have corresponding rows in the FK table.
• Attempting to update the PK attribute value that does not have corresponding FK rows with the same value.

• Cascade delete: delete the corresponding FK rows before deleting the PK row.
• Nullify delete: update the corresponding FK rows to NULL before deleting the PK row.

• Cascade update: update the corresponding FK attribute before updating the PK attribute.
• Nullify update: update the corresponding FK attribute to NULL before updating the PK attribute.

Procedure

Implement a PK-FK constraint

To create a Primary Key/Foreign Key constraint between two tables, first identify the primary key field on the parent table. A primary key field must satisfy two conditions; it must be Unique and NOT NULL. Alternatively, create a unique key on the Parent table which is referenced by the FK.

Once a PK is defined, other tables can create a FK constraint. The FK constraint is defined on the child table where the FK attribute corresponds to the parent PK attribute.

In the following example, STREETS primary key (STREET_ID) is related to ACCIDENTS foreign key (ST_ID).

Once the PK-FK behavior has been established, edits to these tables must be performed in the correct order or the referential constraint violation occurs. The following steps explore workflow options for working with DBMS referential integrity.

• PK-FK with default behavior
  PK-FK referential integrity constraints without CASCADING behavior results in the following conditions; the PK attribute must be unique and NOT NULL and the FK attribute must have a corresponding row in the PK or be NULL.
  
  Editing workflow on Parent table
  1. Insert to Parent table.
     • When finished creating a new feature, ArcGIS flushes the edit to the DBMS. If the PK attribute has not been set, the PK constraint fails.
     • To prevent returning a PK violation, use the Attribute Inspector to insert the correct PK attribute before flushing the edit to the DBMS. Update the PK value to a unique, non NULL value and then insert the feature to the DBMS.

     • Note:
       Use the Editor > Options dialog box to display the Attribute Inspector before storing the feature.
       
       Additionally, ArcGIS automatically sets the value of any fields with a NOT NULL constraint to avoid violating the DBMS constraint. For example, numeric attributes receives a value of 0. Text fields receive a blank space and date fields the value of ‘Dec 30, 1899’. These arbitrary values are used to ensure the NOT NULL constraint is not violated.

  2. Update Parent PK attribute.
     • Updates which occur to the PK attribute on the Parent table are permissible as long as the update meets the following conditions: If attempting to update the PK attribute to a NULL or a non-unique value, a DBMS integrity violation error message is displayed and the edit is abandoned.
       
       If attempting to update the PK attribute which currently has related records in the child, the DBMS constraint is violated. This is because the update would result in FK records that do not have a match in the PK. The edit in ArcMap is abandoned with an error message similar to:
       
       "Attribute update: An unexpected failure occurred. Underlying DBMS error[ORA-02292:integrity constraint(GIS.STREETS_FK) violated-child record found [GIS.STREETS]"
       
       To update the PK attribute, update the Child table FK attribute. Once the PK record no longer has any related records in the Child table, perform the update on the Parent table.
       
       To update the Child table FK record, perform one of the following options: Once the Parent record has been updated, return to the Child table and update the FK value that was previously changed.
       • Update PK attribute to a unique, not NULL value.
       • The original PK value can not have any related FK rows in the child table.
       • Set the FK to NULL.
       • Change the FK to a different valid value.
       • If the Child table records are no longer required, delete the related records.
  3. Delete Parent record.
     • To satisfy the FK-PK constraint, delete any Parent record that does not have a match in the Child. However, the delete is prevented if the record has any matching child records and the following error message is displayed:
       
       "Delete feature: An unexpected failure occurred. Underlying DBMS error[ORA-02292:integrity constraint(GIS.STREETS_FK) violated-child record found [GIS.STREETS]"
       
       To delete the Parent record, update the related rows in the Child table as described above.
  Editing workflow on Child table:
  1. Insert to Child table.
     • When inserting a new record to the Child table, the FK-PK constraint is not violated if the FK value is NULL. A integrity violation error message is displayed when trying to insert a new record with an FK value that does not have a related value in the PK. Either insert the Child record with a NULL or with a valid FK value.
  2. Update Child FK attribute.
     • When updating the FK value in the Child, either update the FK to a NULL or to a valid value found in the PK. If a value that is not found in the PK is entered, a DBMS integrity constraint violation is displayed.
  3. Delete Child record.
     • Deleting records in the Child table do not impact the FK-PK constraint.
• PK-FK with ON DELETE CASCADE behavior
  Using the ON DELETE CASCADE behavior ensures when a record is deleted from the Parent table, the corresponding records in the Child table are also deleted. The DBMS automatically deletes the child records so the PK-FK constraint remains valid during a delete operation on the Parent table.
  
  Inserting to the Parent or Child and updating the FK or PK attributes behaves as describe above.

• PK-FK with ON DELETE SET NULL behavior
  Using the ON DELETE SET NULL behavior ensures when a record is deleted from the Parent table, the corresponding FK attributes in the Child table are updated to NULL. The DBMS preserves the referential integrity by nullifying the child FK attributes.
  
  Inserting to the Parent or Child and updating the FK or PK attributes behaves as describe above.

• Workflow with additional constraints on the FK
  Along with creating PK-FK referential integrity, additional constraints can be added to the tables. If adding constraints to the Foreign Key, the editing workflow may be impacted to ensure compliance with the constraints. Two additional constraints that may be applied are NOT NULL and unique constraints:
  • A NOT NULL constraint ensures that the attribute can not be set to NULL.
  • A Unique constraint ensures that the values for a particular attribute must be unique.
  The following information explores editing workflow considerations for implementing PK-FK constraints with NOT NULL and Unique constraints. These considerations are in addition to the editing workflow implications addressed previously.
  • FK-PK with NOT NULL constraint on FK.
    • Before deleting the PK record, either delete the related records, or change the FK value as the FK value to NULL can not be set.
      
      Using the ON DELETE SET NULL behavior fails as the DBMS is attempting to set the FK to NULL which is not permissible based on the additional constraint.
      
      Inserting or updating the FK value must not be NULL and must match a record in the PK.
  • FK-PK with Unique constraint on FK.
    • Attempting to insert or update the FK value with a duplicate value fails. When updating the FK value, it must match a record in the PK and be unique or NULL.
  • FK-PK with NOT NULL and Unique constraints on FK.
    • Before deleting the PK record, either delete the FK record or update the FK value to a different unique value.
      
      Using the ON DELETE SET NULL behavior fails as the DBMS is attempting to set the FK to NULL, which is not permissible based on the additional constraint.
      
      Attempting to insert or update the FK value with a duplicate value fails.
      
      When updating the FK value, it must match a record in the PK and be unique.