ALTER TABLE Statement
|Previous Top Next|
Modifies columns and column types of an existing table.
ALTER TABLE table_reference
DROP column_reference |
DROP CONSTRAINT constraint_name |
ADD column_definition |
ADD CONSTRAINT constraint_definition |
RENAME column_reference TO column_reference |
The ALTER TABLE command enables you to modify the structure of an existing table. Please find the description for column_definition and constraint_definition in the topic about the CREATE TABLE statement. There are six different options:
Delete an existing column with DROP:
ALTER TABLE Orders DROP Destination
The column_reference must refer to an existing column.
Delete an existing constraint with DROP:
ALTER TABLE Orders DROP CONSTRAINT sys_Primary
Add a new column with ADD:
ALTER TABLE Orders ADD Date_of_delivery DATE
The name of the new column must not exist before.
Add a new constraint with ADD, which can be either a named constraint or an unnamed constraint:
ALTER TABLE Orders ADD CONSTRAINT RecentDateConstraint CHECK (Date_of_delivery > 1.1.2000)
ALTER TABLE Orders ADD FOREIGN KEY (Customer) REFERENCES Customer (CustNo)
Modify the name of an existing column with RENAME:
ALTER TABLE Orders RENAME Date_of_delivery TO DateOfDelivery
The first column_reference is the name of an existing column, the second is the new name of this column. Renaming a column keeps the data within the column intact.
Modify the column type of an existing column with MODIFY:
ALTER TABLE Orders MODIFY DateOfDelivery TIMESTAMP
The column_reference must refer to an existing column. You may change the column type to any one of the available column types. The column data is kept as far as possible.
The parameters level_number, password, key and language have the same meaning as in the CREATE TABLE statement. If password and key are omitted, the current settings are kept. To remove the encryption, set it to NONE.
This statement removes encryption from a table:
ALTER TABLE Orders ENCRYPTION None
Note: If the password or the encryption mode are to be changed, both the password and the encryption must be indicated for security reasons.
It is possible to combine multiple changes in any order within one single command:
ALTER TABLE Orders
ADD Date_of_delivery DATE,
ADD DeliveryAddress CHAR(200),
RENAME Customer TO CustomerRef
Note: RENAME and MODIFY are proprietary extensions to SQL-92.
The COLLATE clause is supported from table level 6 on.
The SET clause in the column definition is only supported for table levels greater than 3.