SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting an SQL/MP Database to SQL/MX Tables
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
11-11
Partitions
Constraints
Unlike NonStop SQL/MP, you do not create constraints as separate database objects
for SQL/MX tables. The CREATE CONSTRAINT statement does not exist in NonStop
SQL/MX. In NonStop SQL/MX, use the CREATE TABLE statement to create
constraints on SQL/MX tables, and use the ALTER TABLE statement to add or drop
constraints. NonStop SQL/MX supports more types of constraints than NonStop
SQL/MP, as Table 11-2
shows.
SQL/MX tables support referential integrity and unique constraints, which do not exist
for SQL/MP tables. In NonStop SQL/MP, you can simulate a unique constraint by
creating a unique index. Referential integrity ensures that tables that refer to each
other remain consistent. A table refers to another table by a foreign key, which is a
column or set of columns in the table that match a column or set of columns (usually a
unique or primary key) in the referenced table. A referential integrity constraint ensures
that the foreign key contains only values that match those in a column or set of
columns in the referenced table. A unique constraint ensures that a column or set of
columns contain only one occurrence of a nonnull value or set of values.
For more information about SQL/MX constraints, see the
SQL/MX Reference Manual.
Partitions
In NonStop SQL/MX, use the PARTITION clause of the SQL/MX CREATE TABLE (or
CREATE INDEX) statement to create partitions of an SQL/MX table (or index). Unlike
NonStop SQL/MP, which allows only one partition per disk volume, you can locate as
many SQL/MX partitions as you want on the same disk volume.
NonStop SQL/MX supports decoupled range partitioning.
Decoupled means that the
partitioning key need not be the same as the clustering key. The partitioning key can
be a subset of the clustering key columns, and those columns can be specified in a
different order. Decoupled range partitioning is useful for co-locating range-partitioned
index data on the same disk volume as the underlying table rows.
In addition to range partitioning, NonStop SQL/MX supports hash partitioning of
SQL/MX tables. Hash partitions are better for larger queries that run mostly in parallel
and access all partitions. To generate hash partitions, NonStop SQL/MX uses a hash
function on the values of the partitioning key and assigns each record to a partition
Table 11-2. SQL/MP and SQL/MX Constraints
Type of Constraint
Supported in NonStop
SQL/MP?
Supported in NonStop
SQL/MX?
Check Yes Yes
NOT NULL Yes Yes
Primary key Yes Yes
Referential integrity No Yes
Unique No Yes