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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-21
Considerations for ALTER TABLE
ALTER TABLE works only on user-created tables. You cannot use it to modify a
metadata table even if you are the owner of the metadata tables or a SUPER user.
Adding a Constraint
To add a constraint that refers to a column in another table, you must have
REFERENCES privileges on that column.
Dropping a Constraint
To drop a constraint, you must be owner of the table on which the constraint has been
defined or be the super ID. If you are owner of the table which the referential constraint
is referencing you can revoke the REFERENCE privilege on the column. Revoking the
REFERENCE privileges, in effect, drops the constraint. You can revoke the
REFERENCE privilege with a REVOKE command or indirectly through a DROP
TABLE … CASCADE statement.
Adding a Column
A user who has UPDATE or REFERENCES privileges on a table also has those
privileges on added columns of the table.
Constraints Implemented With Indexes
NonStop SQL/MX uses unique indexes to implement all UNIQUE constraints, including
PRIMARY KEY constraints. Nonunique indexes are used to implement the foreign key
portion of all referential constraints added with ALTER TABLE.
When you add such a constraint, NonStop SQL/MX checks if an existing index can be
used to implement the constraint and if not, automatically creates a new index (if
possible, with the same name as the constraint). It uses the same primary extent size,
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.