SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

CREATE UNIQUE INDEX XEMP
ON persnl.employee (LAST_NAME, EMPNUM)
LOCATION $data1
ATTRIBUTE NO AUDITCOMPRESS
HASH PARTITION
(ADD LOCATION $data2,
ADD LOCATION $data3,
ADD LOCATION $data4,
ADD LOCATION $data5);
Creating Constraints on SQL/MX Tables
Constraints are rules that protect the integrity of data in a table by restricting the values in a
particular column or set of columns to those that meet the conditions of the constraints. NonStop
SQL/MX enforces the constraint criteria when the constraint is created, when table rows are
updated and or inserted, and when table rows are deleted.
Rules for Adding and Dropping Constraints
• To add a constraint that refers to a column in another table, you must have REFERENCE
privileges on that column and own the schema of the table on which you are creating the
constraint.
• To drop a constraint, you must own the schema of the table on which the constraint has been
defined.
For more information, see “Access Privileges for SQL/MX Database Objects” (page 78).
Creating, Adding, and Dropping Constraints
To create constraints on an SQL/MX table when you create the table, use the NOT NULL, UNIQUE,
PRIMARY KEY, CHECK, or FOREIGN KEY REFERENCES clauses of the CREATE TABLE statement.
To add constraints to an existing table, use the UNIQUE, PRIMARY KEY, CHECK, or FOREIGN
KEY REFERENCES clauses of the ALTER TABLE statement.
Define constraints either on a single column (column constraint) or on a set of columns (table
constraint). Create a NOT NULL column constraint by using CREATE TABLE and drop NOT NULL
by using ALTER TABLE. All other constraints can be added or dropped by using ALTER TABLE.
All NOT NULL constraints defined when you create a table are propagated to CHECK constraints.
For example, C1 INT NOT NULL is changed to CHECK, where CHECK C1 is NOT NULL in the
metadata. To add a NOT NULL constraint during an ALTER TABLE operation, you can use a CHECK
constraint. However, this CHECK constraint must be droppable.
Similarly, all column constraints are propagated to table constraints. You can define column
constraints only with CREATE TABLE or ALTER TABLE...ADD COLUMN statements. ALTER
TABLE...ADD COLUMN adds constraints only to the new column being created.
For greater efficiency, specify a NOT NULL or PRIMARY KEY constraint as NOT DROPPABLE at
table creation time. If a NOT NULL NOT DROPPABLE constraint is added to a new column by
using ALTER TABLE, a separate CHECK constraint is added.
If you define several NOT NULL NOT DROPPABLE constraints on a table, NonStop SQL/MX
automatically combines them into one constraint. This approach ensures that, at run time, only one
constraint check is performed. If you perform a SHOWDDL, all constraint information is shown as
one constraint. Combining constraints is implemented only for NOT NULL NOT DROPPABLE
constraints.
To drop a constraint, use an ALTER TABLE statement that specifies the name of the constraint to be
dropped.
For more information, see the SQL/MX Reference Manual.
98 Creating an SQL/MX Database