alter table

Alter the definition of a table

Syntax

alter_action:
          RENAME table_name
     |    ADD [COLUMN] column_def
     |    DROP [COLUMN] column_name
     |    ADD tab_constraint
     |    DROP CONSTRAINT constraint_name
     |    ALTER [COLUMN] column_name alter_column_action

alter_column_action:
          RENAME column_name
     |    SET DEFAULT {constant | CURRENT | NULL | AUTO |
               {CURDATE | CURRENT_DATE} | {CURTIME | CURRENT_TIME} | {CURTS | CURRENT_TIMESTAMP}}]
     |    DROP DEFAULT
     |    SET NOT NULL
     |    DROP NOT NULL
     |    SET DATA TYPE type_def

Description

An ALTER TABLE statement is used to make a change to the definition of table.

Note that the SQL standard only allows one alter_action per ALTER TABLE statement.

If specified, the database_name, must match that associated with the most recently issued ALTER SCHEMA statement or if no ALTER SCHEMA statement has been issued then an implicit ALTER SCHEMA is assumed.

You can use ALTER TABLE to RENAME the table, ADD or DROP columns in the table, and to ADD or DROP table constraints. The ALTER COLUMN clause can be used to RENAME the column, change or DROP a column’s DEFAULT value, SET or DROP the NOT NULL constraint, and make an adjustment to the column’s data type. For SET DATA TYPE, the SQL standard does not allow the data type to be changed but only allows the declared lengths or precisions to be increased. Hence, RDM only allows data type changes where the change increases the numeric precision (e.g., change from int16 to int32) or where the length of string type columns to be increased.

For ADD COLUMN definitions the NOT NULL and UNIQUE or PRIMARY KEY constraints are not allowed.

A DROP COLUMN is not allowed for columns that are referenced in a constraint (KEY, REFERENCES, CHECK clauses). Those constraints must be dropped first before dropping the column.

For ADD tab_constraint, the current table rows will be scanned as necessary to check conformance to the new constraint. If any of the existing rows do not conform, then the ALTER will fail and the DDL transaction will abort. A PRIMARY KEY cannot be added to a table that already has one defined. When adding a new KEY to a table, the AVL_TREE indexing method is not currently allowed.

Adding and dropping columns, changing or dropping default values, dropping constraints, renaming tables and columns, and adjusting the data types do not require any conversions on the existing database data so that those types of changes will take immediate effect when the changes are committed. Only adding new constraints such as keys (where the new index needs to be generated) or NOT NULL (where all existing rows must be checked to ensure there are no null column values) will require extra time to validate or incorporate the new changes into the database at commit time.