Dropping Existing Constraints from a Column

Go Up to Using ALTER TABLE

You must drop constraints from a column in the correct sequence. See the following CREATE TABLE example. Because there is a foreign key in the PROJECT table that references the primary key (EMP_NO) of the EMPLOYEE table, you must first drop the foreign key reference before you can drop the PRIMARY KEY constraint in the EMPLOYEE table.

CREATE TABLE PROJECT
(PROJ_ID PROJNO NOT NULL,
 PROJ_NAME VARCHAR(20) NOT NULL UNIQUE,
 PROJ_DESC BLOB(800,1),
 TEAM_LEADER EMPNO,
 PRODUCT PRODTYPE,
 PRIMARY KEY (PROJ_ID),
 CONSTRAINT TEAM_CONSTRT FOREIGN KEY (TEAM_LEADER) REFERENCES
 EMPLOYEE (EMP_NO));

The proper sequence is:

ALTER TABLE PROJECT
 DROP CONSTRAINT TEAM_CONSTRT;
ALTER TABLE EMPLOYEE
 DROP CONSTRAINT EMP_NO_CONSTRT;
ALTER TABLE EMPLOYEE
 DROP EMP_NO;

Note: Constraint names are in the system table, ­RDB$RELATION_CONSTRAINTS.

In addition, you cannot delete a column if it is referenced by another column’s CHECK constraint. To drop the column, first drop the CHECK constraint, then drop the column.