SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Planning Database Security and Recovery
HP NonStop SQL/MX Installation and Management Guide—544536-007
5-38
Fixup of Tables with Referential Integrity Constraints
>>drop table tab32k;
--- SQL operation complete.
Fixup of Tables with Referential Integrity
Constraints
SQL/MX 2.3.4 and later releases support the referential integrity (RI) actions
CASCADE, SET DEFAULT and SET NULL. Falling back to SQL/MX versions earlier to
2.3.4 results in tables (that include the RI actions) exhibiting the NO ACTION behavior
for DML operations.
Although tables with the RI actions exhibit the NO ACTION behavior for DML queries
in the lower version node (including SHOWDDL), these actions do not change to NO
ACTION automatically in the metadata.
This results in the following change in behavior:
•
If the node is upgraded to SQL/MX 2.3.4 or later versions, the RI ACTION is
exhibited rather than NO ACTION. Therefore, the RI actions (CASCADE, SET
DEFAULT, or SET NULL) that behaved as NO ACTION in the node of the earlier
version will exhibit the original behavior.
To avoid the change in behavior, the RI ACTIONS must be converted to NO ACTION
using the following methods:
•
Dropping the RI constraints with RI actions and recreating them without the RI
actions.
•
Use the mxtool fixup command to convert the RI actions to NO ACTION. An
option –dc is provided in the mxtool fixup command to support this
functionality; the command is required for each table that has RI constraints with
the RI actions. This skips the data validation process that is performed while
creating the referential integrity constraint. For more information and examples of
this command, see the SQL/MX Reference Manual.
Before falling back to a version earlier to SQL/MX 2.3.4, all the tables with the RI
actions must be converted to NO ACTION.
The following query can be used to list all the tables in a catalog that have the RI
constraint with the new RI actions.
SELECT
CA.CAT_NAME "Catalog",
S.SCHEMA_NAME "Schema",
O1.OBJECT_NAME "Table name",
O.OBJECT_NAME "Constraint Name",
R.DELETE_RULE "Delete rule",
R.UPDATE_RULE "Update rule"
FROM
NONSTOP_SQLMX_<node_name>.SYSTEM_SCHEMA.CATSYS CA,
NONSTOP_SQLMX_<node_name>.SYSTEM_SCHEMA.SCHEMATA S,