SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-35
Creating Constraints on SQL/MX Tables
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 about security and access privileges for SQL/MX database
objects, see Access Privileges for SQL/MX Database Objects on page 7-9.
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.