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.