SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-25
Considerations for ALTER TABLE
Considerations for ALTER TABLE
You cannot use ALTER TABLE to change a partition’s name.
Effect of Adding a Column on View Definitions
The addition of a column to a table has no effect on existing view definitions. Implicit
column references specified by SELECT * in view definitions are replaced by explicit
column references when the definition clauses are originally evaluated.
Authorization and Availability Requirements
To alter a table, you must own the schema or be the super ID or object owner. You
must also have access to all partitions of the table itself.
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 the schema or object 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.
Renaming a Table
You can use the rename option to change the name of a table. The following are the
prerequisites for renaming a table:
•
The new ANSI name must not already exist within the schema.
•
The table and all of the partitions must be available. Also, all partitions of indexes
of the table must be available.
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.