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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-22
Examples of ALTER TABLE
Indexes used to enforce constraints can require significant amounts of disk space, and
NonStop SQL/MX might be unable to create the supporting index when you add the
constraint. Consequently, the add constraint operation fails.
Adding CHECK and FOREIGN KEY Constraints
When a CHECK or FOREIGN KEY constraint is added to a table containing data, the
existing data is validated to ensure it conforms to the constraint. While this validation
takes place, the table is locked for read-only access. For a FOREIGN KEY constraint,
both the referencing and referenced tables are locked. This means that while SQL can
perform statement compilations that use these tables or perform updates to these
tables, you will not be able to update the data.
A full-file scan that is run in a single TMF transaction could experience TMF limitations,
such as transaction timeout, if a large amount of data is to be checked.
Dropping FOREIGN KEY Constraints
To drop a table’s foreign key, you must perform SHOWDDL on the table to find the
constraint’s system identification, then use that value in the ALTER TABLE statement.
For a description of SHOWDDL, see SHOWDDL Command on page 4-71. For an
example of an ALTER TABLE statement to drop a foreign key, see Examples of ALTER
TABLE.
SQL/MX Extensions to ALTER TABLE
The SQL/MX extensions are:
•
ATTRIBUTES clause
•
ASCENDING and DESCENDING options on the PRIMARY KEY constraint
Considerations for Referential Integrity
For information on referential integrity constraints, see the Considerations for
Referential Integrity section in CREATE TABLE
Examples of ALTER TABLE
•
This example adds a UNIQUE table constraint:
ALTER TABLE persnl.project
ADD CONSTRAINT projtimestamp_uc
UNIQUE (projcode, ship_timestamp);
Note. When using a large table, you should create the supporting index before you create a
constraint. As a result, you can create the index as needed (for example, with partitions) so
that you have better control over use of disk volumes. To create the constraint, you must create
a unique or nonunique index before retrying the ALTER TABLE ADD CONSTRAINT operation.
You might also want to partition the supporting index for better performance.