SQL/MX 3.x Reference Manual (H06.22+, J06.11+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—640322-001
2-96
Considerations for CREATE TABLE
NonStop SQL/MX will place the primary partition on $DATA1, the second partition
on $VOL1, the third partition on $VOL2, and the fourth partition on $VOL3.
Creating a Table Without STORE BY Clause or Primary Key
NonStop SQL/MX bases table partitioning on clustering key columns, specified by the
STORE BY clause or, if there is no STORE BY clause, the primary key columns. If you
do not specify the STORE BY or PRIMARY KEY columns on a table, NonStop
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. POS can be used to create partitions automatically for
tables with decoupled keys.
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.