SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—540440-003

2-18

Syntax Description of ALTER TABLE

specify on a PRIMARY KEY constraint cannot match the set of columns on any

UNIQUE constraint for the table.

ASCENDING and DESCENDING specify the direction for entries in each column

within the key. The default is ASCENDING.

The PRIMARY KEY value in each row of the table must be unique within the table.

Columns within a PRIMARY KEY cannot contain nulls. A PRIMARY KEY defined

for a set of columns implies that the column values are unique and not null.

When a PRIMARY KEY table constraint is added by using the ALTER TABLE

statement, it is always droppable. For a PRIMARY KEY column constraint, you

cannot specify NOT DROPPABLE; if you do, NonStop SQL/MX returns an error.

A PRIMARY KEY constraint is enforced with a unique index. If there is already a

user-defined unique index on key-column-list, NonStop SQL/MX uses this

index; if not, it creates a unique index. Because the PRIMARY KEY constraint uses

a supporting unique index, the clustering key is not part of the constraint definition.

Therefore, the maximum combined length of the columns for the PRIMARY KEY is

255 bytes.

When a PRIMARY KEY constraint is created on a table, all the index columns must

have a NOT NULL clause in the CREATE TABLE statement for the table.

The value of the PRIMARY_KEY_CONSTRAINT_DROPPABLE_OPTION attribute

in the DEFAULTS Table has no affect on a PRIMARY KEY constraint added by

using the ALTER TABLE statement because in this case the PRIMARY KEY is

always droppable.

CHECK (condition)

is a constraint that specifies a condition that must be satisfied for each row in

the table.

NonStop SQL/MX checks the condition whenever an insert or update operation

occurs that might affect its value. The operation is allowed if the predicate in the

search condition evaluates to TRUE or null, but is prohibited if the predicate

evaluates to FALSE. When a check constraint is added, existing data is checked

for violations.

You cannot refer to the CURRENT_DATE, CURRENT_TIME, or

CURRENT_TIMESTAMP function in a CHECK constraint, and you cannot use

subqueries in a CHECK constraint.

See Search Condition on page 6-102.

REFERENCES ref-spec

FOREIGN KEY (column-list) REFERENCES ref-spec

is a column or table constraint (respectively) that specifies a referential constraint

for the table, declaring that a column or set of columns (called a foreign key) in