SQL/MP to SQL/MX Database and Application Migration Guide

Converting SQL/MP Tables to SQL/MX Tables
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001
5-10
Tables
For more information about SQL/MX data types, see the SQL/MX Reference Manual.
Primary Keys
In both NonStop SQL/MP and NonStop SQL/MX, primary keys specify a column or
group of columns whose values uniquely identify the rows in a table. In NonStop
SQL/MP, the primary key defines the way data is organized in the underlying Guardian
files. In NonStop SQL/MX, the primary key is a constraint defined by the ANSI SQL
standard. However, in NonStop SQL/MX, you can define the primary key as NOT
DROPPABLE, which is the default, so that the primary key works the same as in
NonStop SQL/MP. For more information about SQL/MX primary keys, see the
SQL/MX
Reference Manual
.
Clustering Keys
Unlike NonStop SQL/MP, the SQL/MX CREATE TABLE statement does not have a
CLUSTERING KEY clause. In NonStop SQL/MX, use the STORE BY clause of the
CREATE TABLE statement to assign a clustering key for a SQL/MX table. If you
specify STORE BY PRIMARY KEY, NonStop SQL/MX bases the clustering key on the
primary key. The STORE BY clause determines the order of rows in the physical file
and affects how you can partition the file. For more information about SQL/MX
clustering keys, see the
SQL/MX Reference Manual.
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 5-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
Table 5-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