ASA SQL User's Guide
Ensuring Data Integrity
Enforcing entity and referential integrity
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.
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:
Delete or reassign the employees belonging to that department.
Redo the search condition on a SELECT statement to select the rows that violate referential integrity.
Insert the dept_id row back into the department table.
Roll back the transaction to undo the DELETE operation.