Modifies columns and column types of an existing table.

Syntax

ALTER TABLE table_reference
[LEVEL level_number]
[ENCRYPTION encryption_algorithm]
[PASSWORD password]
[LANGUAGE language]
DROP column_reference |
DROP constraint_name |
ADD column_definition |
ADD constraint_definition |
RENAME column_reference TO column_reference |
MODIFY column_definition

Description

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,
DROP Destination,
ADD DeliveryAddress CHAR(200),
RENAME Customer TO CustomerRef

Note: RENAME and MODIFY are proprietary extensions to SQL-92.

Compatibility Information

The LANGUAGE clause is not supported by TurboDB Managed.

See also

Column Data Types