SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-18
Syntax Description of ALTER TABLE
If you do not specify a constraint name, NonStop SQL/MX constructs an SQL
identifier as the name for the constraint in the catalog and schema for table.The
identifier consists of the fully qualified table name concatenated with a system-
generated unique identifier. For example, a constraint on table A.B.C might be
assigned a name such as A.B.C_971..._01... .
UNIQUE
or
UNIQUE (column-list)
is a column or table constraint (respectively) that specifies that the column or set of
columns cannot contain more than one occurrence of the same value or set of
values. If you omit UNIQUE, duplicate values are allowed unless the column is part
of the PRIMARY KEY.
column-list cannot include more than one occurrence of the same column. In
addition, the set of columns you specify on a UNIQUE constraint cannot match the
set of columns on any other UNIQUE constraint for the table or on the PRIMARY
KEY constraint for the table. All columns defined as unique must be specified as
NOT NULL.
A UNIQUE constraint is enforced with a unique index. If there is already a unique
index on column-list, NonStop SQL/MX uses this index; if not, the system
creates a unique index.
PRIMARY KEY [ASC[ENDING] | DESC[ENDING]] DROPPABLE]
or
PRIMARY KEY key-column-list
is a column or table constraint (respectively) that specifies a column or set of
columns as the primary key for the table. key-column-list cannot include more
than one occurrence of the same column. In addition, the set of columns you
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.