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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-95
Considerations for CREATE TABLE
SQL/MX cannot partition the table. If you attempt to use POS with such a table, you
will not receive an error. POS creates a nonpartitioned table in the same way that
NonStop SQL/MX creates a nonpartitioned table without the LOCATION clause as part
of the CREATE TABLE statement. The location of this table is not based on
POS_LOCATIONS or automatic disk location.
Partitioning Columns
Use the PARTITION BY clause to decouple the partitioning key from the clustering key.
Without the PARTITION BY clause, the partitioning columns of the table are same as
the clustering key columns. When you use the POS feature, you cannot choose
partitioning columns for automatically created partitions. The partitioning columns of
automated partitioned tables created through MXCS/JDBC sessions using POS are
the same as the clustering key columns of the table.
SQL/MX Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. SQL/MX
extensions to the CREATE TABLE statement are [NOT] DROPPABLE, ASCENDING,
DESCENDING, STORE BY, LOCATION, PARTITION, ATTRIBUTE, and LIKE clauses.
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