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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-21
Considerations for ALTER TABLE
secondary extent size and MAXEXTENTS values as the base table's primary
partition.The index is created on the same volume as the base table's primary partition.
NonStop SQL/MX then populates the new index.
After NonStop SQL/MX populates the index, you should perform a FUP RELOAD on
the index and all its partitions, to organize the index structure more efficiently and to
reduce index levels.
If you are creating a constraint on a large table, you might receive an error 45 (file full).
In addition, because NonStop SQL/MX executes the creation of the constraint in a
single TMF transaction, you might experience TMF limitations such as a full audit trail
file or transaction timeout.
If you create an index with the default values by mistake, you might need to re-create
the index. You can alter maxextents size after the index is created, but you cannot alter
primary and secondary extent sizes.You can use MODIFY to partition the index and
move the partitions to desired locations.
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-67. For an
example of an ALTER TABLE statement to drop a foreign key, see Examples of ALTER
TABLE.
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.