Contents Index Referential integrity actions Integrity rules in the system tables

ASA SQL User's Guide
  Ensuring Data Integrity
    Enforcing entity and referential integrity

Referential integrity checking


For foreign keys defined to RESTRICT operations that would violate referential integrity, default checks occur at the time a statement executes. If you specify a CHECK ON COMMIT clause, then the checks occur only when the transaction is committed.

Using a database option to control check time 

Setting the WAIT_FOR_COMMIT database option controls the behavior when a foreign key is defined to restrict operations that would violate referential integrity. The CHECK ON COMMIT clause can override this option.

With the default WAIT_FOR_COMMIT set to OFF, operations that would leave the database inconsistent cannot execute. For example, an attempt to DELETE a department that still has employees in it is not allowed. The following statement gives the error primary key for row in table 'department' is referenced in another table:

DELETE FROM department
WHERE dept_id = 200

Setting WAIT_FOR_COMMIT to ON causes referential integrity to remain unchecked until a commit executes. If the database is in an inconsistent state, the database disallows the commit and reports an error. In this mode, a database user could delete a department with employees in it, however, the user cannot commit the change to the database until they:


Contents Index Referential integrity actions Integrity rules in the system tables