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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-20
Considerations for ALTER TABLE
Unlike NonStop SQL/MP’s form of this statement, SQL/MX’s ALTER TABLE
statement has no PARTONLY clause. When you supply a new value for attributes,
ALTER TABLE modifies the value of the attribute on all partitions of the table. For
more detail, see the entry for a specific attribute.
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 its schema or be the super ID. 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 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,