SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-96
Considerations for CREATE TABLE
•
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.
Usage and Performance
The RI actions CASCADE, SET NULL, and SET DEFAULT enable you to maintain
data integrity between tables. Performing RI actions is resource-intensive because
indexes and multiple tables are involved, which can result in a significant drop in
performance of queries when a large dataset is involved. Therefore, it is important that
you consider the performance implication while defining RI relationships.
Versioning Considerations
SQL/MX versions earlier to 2.4 do not check for RI actions when an update or delete
operation is done on the primary table. By default, the RI actions in these cases will be
NO ACTION. Therefore when you downgrade to MXV, any update/delete statement on
the referred table, compiled on the downgraded node produces a plan with the RI
action - NO ACTION, irrespective of the RI action present in the metadata. The
metadata of the downgraded node can be made consistent with the RI action exhibited
by using a new option in the FIXUP utility.
Other Considerations
•
An RI constraint with the new RI actions cannot be created on a remote table
which is on a node with MX software version earlier to 2.4.
Consider an RI with an RI action other than NO ACTION or RESTRICT. When the
primary table is deleted and the delete action in the RI of the foreign table is
CASCADE, the row trigger on the foreign table with DELETE as the trigger operation
is fired if there are matching rows in the foreign table. Similarly, when the primary table
is updated and the update action is anything except NO ACTION or RESTRICT, the