Handling cycles in referential integrity

A cycle exists when a series of primary-key-foreign-key relationships exists within a group of tables in a database.

Example: Table reference cycle

In the following example, the parts.distrib_no column references the primary key of the distributor table, and the distributor.part_no column references the primary key of the parts table. Each of the tables references the other, forming a cycle.

CREATE TABLE parts
(
part_no INTEGER NOT NULL PRIMARY KEY,
part_name CHAR (19),
distrib_no INTEGER REFERENCES distributor
) ;
CREATE TABLE distributor
(
distrib_no INTEGER NOT NULL PRIMARY KEY,
distrib_name CHAR (19),
address CHAR (30),
phone_no CHAR (10),
part_no INTEGER REFERENCES parts
) ;

Example: Single-table reference cycle

A special case of the cycle in referential integrity occurs when a foreign key of a table references the primary key of the same table. The following example shows this single-table cycle.

CREATE TABLE employee
(
empno INTEGER NOT NULL PRIMARY KEY,
ename CHAR (30) NOT NULL,
deptno INTEGER NOT NULL,
mgr_code INTEGER REFERENCES employee(empno)
) ;

In this section:

Creating tables in cycles

Inserting rows in a cycle