Extended Referential Integrity

A Technical Information Article by FFE Software, Inc.

Normally, a foreign key in one table references the primary key in another table. In some cases, a foreign key may reference the primary key of the same table. This is known as self-referencing foreign keys.

In a related case, the foreign key of one table may reference the primary key of a second table. The second table also has a foreign key, and it references the primary key of the first table. This is known as cross-referencing foreign keys.

Cross-referencing and self-referencing foreign keys are a special case of Referential Integrity. While not well known, they have many real world uses. For examples, we will use an Employee table containing the fields,

EmpNo, Name, SSN, Salary, DeptNo, MgrNo The primary key for this table is EmpNo (Employee Number). DeptNo (Department Number) is a foreign key referencing another table - the Department table, which we will describe shortly. MgrNo (Manager Number) is also a foreign key, but it references the Employee table itself, an example of a self-referencing foreign key. The Manager Number in an Employee table row matches the Employee Number of the employee's manager.

The second table is a Department table containing the fields,

DeptNo, Name, Location, MgrNo DeptNo (Department Number) is the primary key. MgrNo (Manager Number) is a foreign key referencing the Employee table. It identifies the Employee Number of the department manager. The Employee and Department tables are cross-referencing tables. The Employee table has a foreign key referencing the Department table, and the Department table a foreign key referencing the Employee table.

Self-referencing foreign keys produce a recursive tree structure - like a part explosion. For the Employee table it is an organization chart:

The obvious question here is, who is the manager of the President? You could say the Board of Directors or the stock holders are the managers of the President, but they are often not employees. The Manager Number for the President could be Null (no manager in the organization chart), or it could contain the Employee Number of the President himself - a direct self-reference.

Cross-referencing also produces some interesting lattices. A single department might look like this,

In other cases, cross-referencing might produce a zig-zag chain :

The structure could also be circular :

Cross-referencing may also involve more than two tables. Any number of tables may be hooked together in a circular chain of foreign key references.

Creation and maintenance of self-referencing and cross-referencing foreign keys has some special considerations. These are discussed in the following sections:

Issue Papers Return To Issues Page

Copyright © 1998 FFE Software, Inc. All Rights Reserved WorldWide