Dropping a Foreign Key

Drop a foreogn key from a table.

Create the primary table and foreign table. For example:

CREATE TABLE DEPT(DeptNo int primary key,
DeptName varchar(20),
Mgr int );

CREATE TABLE EMPLOYEE(EmpNo int primary key,
DeptNo int references DEPT(DeptNo)
on delete restrict,
LastName varchar(20),
FirstName varchar(20),
Salary int);

When there is no role name assigned, the default role name for the specified foreign key is DEPT:
```
ALTER TABLE EMPLOYEE DROP FOREIGN KEY DEPT;
```
If there are multiple foreign keys and the role name is unknown, you can use the sp_iqconstraint procedure to display it. See Reference: Building Blocks, Tables, and Procedures.
If a role name was assigned, EMP_DEPT, for example, you must specify it when dropping the key:
```
ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEPT;
```

These statements do not drop the non-unique HG index for EMPLOYEE(DeptNo) which is implicitly created. To drop it, use sp_iqindex to find the HighGroup index name and use the DROP INDEX statement, as follows:

sp_iqindex(‘EMPLOYEE’);

EMPLOYEE DBA DeptNO FP ASIQ_IDX_T27_C2_FP N
EMPLOYEE DBA DeptNO HG ASIQ_IDX_T27_C2_HG N
EMPLOYEE DBA EmpNO FP ASIQ_IDX_T27_C1_FP N
EMPLOYEE DBA EmpNO HG ASIQ_IDX_T27_I11_HG N
EMPLOYEE DBA FirstName FP ASIQ_IDX_T27_C4_FP N
EMPLOYEE DBA LastName FP ASIQ_IDX_T27_C3_FP N
EMPLOYEE DBA Salary FP ASIQ_IDX_T27_C5_FP N

DROP INDEX ASIQ_IDX_T27_C2_HG

To drop a table, you must drop all associated foreign key constraints. Drop foreign key constraint and tables in this order:

ALTER TABLE DROP FOREIGN KEY MGR_EMPNO;
DROP TABLE EMPLOYEE;
DROP TABLE DEPT;

Another way to drop the same tables would be to use the following two ALTER TABLE statements in any order and then do DROP TABLE statements in any order:

ALTER TABLE DEPT DROP FOREIGN KEY MGR_EMPNO;
ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEPT;

Suppose that the database also contained an office table, listing office locations. The employee table might have a foreign key for the office table that indicates where the employee's office is located. The database designer may allow for an office location not being assigned when the employee is hired. In this case, the foreign key should allow the NULL value for when the office location is unknown or when the employee does not work out of an office.