Dropping an Existing Column from a Table

Go Up to Using ALTER TABLE

You can use ALTER TABLE to delete a column definition and its data from a table. A column can be dropped only by the owner of the table. If another user is accessing a table when you attempt to drop a column, the other user’s transaction will continue to have access to the table until that transaction completes. InterBase postpones the drop until the table is no longer in use.

The syntax for dropping a column with ALTER TABLE is:

ALTER TABLE name DROP colname [, colname ...];

For example, the following statement drops the EMP_NO column from the EMPLOYEE table:

ALTER TABLE EMPLOYEE DROP EMP_NO;

Multiple columns can be dropped with a single ALTER TABLE statement.

ALTER TABLE EMPLOYEE
 DROP EMP_NO,
 DROP FULL_NAME;

Important: You cannot delete a column that is part of a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. In the previous example, EMP_NO is the ­PRIMARY KEY for the EMPLOYEE table, so you cannot drop this column unless you first drop the PRIMARY KEY constraint.