SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-136
Considerations for CREATE TABLE
Considerations for Referential Integrity
Circular Dependency
The following situations cause circular dependency when adding a Referential Integrity
(RI)/Trigger:
•
A situation where the UPDATE/DELETE/INSERT operations on the table being
modified invoke RI(s)/trigger(s), thereby re-invoking the same RI/trigger with the
same operation as the RI/trigger invoked earlier. This is an example of a circular
dependency situation, which does not allow you to create this RI/trigger.
Exception: If the circular dependency path consists of only triggers, the situation is
not considered circular dependency for the reasons of backward compatibility.
•
A situation where a few tables are interconnected by RIs, such that the referencing
columns of one RI are the same as the referenced columns of another RI. This is
another example of a circular dependency situation, which does not allow you to
create this RI.
Conflicting and Duplicate Constraints
A referential integrity constraint that is created with new RI actions can conflict or be a
duplicate of the already existing columns.
Conflicting Constraints
•
The two constraints in a table conflict if the referenced table is the same and the
referencing columns overlap or
•
The two constraints of a table conflict if the referenced table and the referencing
columns are the same and are in the same order, but the RI actions are different.
Duplicate Constraints
The two constraints of a table are said to be duplicate if the referenced table and the
referencing columns are in the same order and the RI actions are the same.
If the existing RI actions for both the update and delete rule are NO
ACTION/RESTRICT, and if the newly added RI constraint also has RI actions NO
ACTION/RESTRICT for both the update and delete rule, they are not said to be
duplicate or conflicting. This is to support backward compatibility.
Utilities
The utilities Backup/Restore, MXExportDDL/MXImportDDL, and NSM web support the
newly added RI actions CASCADE/SET NULL/SET DEFAULT in addition to NO
ACTION and RESTRICT.
The utilities DUP and PurgeData retain their existing behavior. The DUP utility does not
support the RI constraints duplication and Purgedata does not allow you to purge data
from a referred table.