Foreign Keys

Use the CREATE TABLE statement or ALTER TABLE statement to create foreign keys, as you do primary keys.

Note: You cannot create foreign key constraints on local temporary tables. Global temporary tables must be created with ON COMMIT PRESERVE ROWS.

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, and, in this example, is mandatory. 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 was allowed to reference a column with duplicate entries in the department table, there is no way to determine which row in the department table is the appropriate reference. This is a mandatory foreign key.