ALTER TABLE Statement
|Previous Top Next|
Modifies columns and column types of an existing table.
ALTER TABLE table_reference
DROP column_reference |
DROP constraint_name |
ADD column_definition |
ADD 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. Note that TurboSQL column names are case-sensitive.
Delete an existing constraint with DROP:
ALTER TABLE Orders DROP PrimaryKey
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:
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 LANGUAGE clause is not supported by TurboDB Managed.