Foreign Key Example

The demo database contains an employee table and a department table. The primary key for the employee table is the employee ID, and the primary key for the department table is the department ID.

For example, assume the following schema:

DEPT table
{ DeptNo int primary key
DeptName varchar(20),
Mgr int,
foreign key MGR_EMPNO (Mgr) references EMPLOYEE(EmpNo) on update restrict }

EMPLOYEE table
{ EmpNo int primary key,
DeptNo int references DEPT(DeptNo) on delete restrict,
LastName varchar(20),
FirstName varchar(20),
Salary int }

In the employee table, the department ID is a foreign key for the department table; each department ID in the employee table corresponds exactly to a department ID in the department table.

The foreign key relationship is a many-to-one relationship. Several entries in the employee table have the same department ID entry, but the department ID is the primary key for the department table, and so is unique. If a foreign key could reference a column in the department table containing duplicate entries, there would be no way of knowing which row in the department table is the appropriate reference. This is a mandatory foreign key.