Dropping an Existing Column

Go Up to Altering a Table

To delete a column definition and its data from a table, use ALTER TABLE. A column can only be dropped by the owner of the table, the SYSDBA, or a user with root privileges. If a table is in use when a column is dropped, the drop is postponed until the table is no longer in use. The syntax for dropping a column with ALTER TABLE is:

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

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

EXEC SQL
ALTER TABLE EMPLOYEE DROP EMP_NO;
EXEC SQL
COMMIT;

Multiple columns can be dropped with a single ALTER TABLE. The following statement drops the EMP_NO and FULL_NAME columns from the EMPLOYEE table:

EXEC SQL
ALTER TABLE EMPLOYEE
DROP EMP_NO,
DROP FULL_NAME;
EXEC SQL
COMMIT;

Deleting a column fails if the column is part of a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. To drop the column, first drop the constraint, then the column.

Deleting a column also fails if the column is used by a CHECK constraint for another column. To drop the column, first drop the CHECK constraint, then drop the column.

For more information about integrity constraints, see the Data Definition Guide.