Enforcing Referential Integrity in a New Table

When creating a new table, enforce referential integrity as follows:

To enforce referential integrity:

Create the primary table, for example:

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

Create the foreign table. For example, in this statement, the default role name for the specified foreign key is DEPT:

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

Add the foreign key constraint. For example:

ALTER TABLE DEPT ADD FOREIGN KEY MGR_EMPNO(Mgr) REFERENCES EMPLOYEE(EmpNo);

Example 1

Another way to create the foreign table follows. In this statement, the user specified role name for the same foreign key is EMP_DEPT:

CREATE TABLE EMPLOYEE(EmpNo int primary key,
DeptNo int,
LastName varchar(20),
FirstName varchar(20),
Salary int,
FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo));

To drop a foreign key constraint.

When there is no role name assigned, as in the first CREATE TABLE example, 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.
In the second CREATE TABLE example, the role name EMP_DEPT was assigned, so you must specify it when dropping the key, as follows:
```
ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEPT;
```

Example 2

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

Example 3

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;

Example 4

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.