Handling Cycles in Referential Integrity

A cycle is formed when there are a list of base tables, where the first table has a foreign key that references the second table, the second table has a foreign key that references the third table and so on and the last table has a foreign key that references the first table.

Consider the following examples.

Example Creating Tables in “Cycles”

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

) ;

In the above example, the column distrib_no of table parts references the primary key of the table distributor and the column part_no of table distributor references the primary key of table parts. That is, each of the two tables is referencing the other. Hence a cycle is formed.

A special case of the cycle in referential integrity is when a foreign key of a table references the primary key of the same table. The following example shows an example of such a cycle:

Example Foreign Key Referring to Same Table

CREATE TABLE employee (

      empno      INTEGER NOT NULL PRIMARY KEY,

      ename      CHAR (30) NOT NULL,

      deptno     INTEGER NOT NULL,

      mgr_code   INTEGER REFERENCES employee(empno)

) ;