Referential cycles

A referencing table and a referenced table need not be distinct. A table may contain a foreign key that references itself. This is called a self-referencing table. A self-referencing table is a special case of a referential cycle.

Example 1

The SQL Anywhere sample database has one table holding employee information and one table holding department information:

CREATE TABLE "GROUPO"."Employees" (
    "EmployeeID"                     int NOT NULL
   ,"ManagerID"                      int NULL
   ,"Surname"                        "person_name_t" NOT NULL
   ,"GivenName"                      "person_name_t" NOT NULL
   ,"DepartmentID"                   int NOT NULL
   ,"Street"                         "street_t" NOT NULL
   ,"City"                           "city_t" NOT NULL
   ,"State"                          "state_t" NULL
   ,"Country"                        "country_t" NULL
   ,"PostalCode"                     "postal_code_t" NULL
   ,"Phone"                          "phone_number_t" NULL
   ,"Status"                         char(2) NULL
   ,"SocialSecurityNumber"           char(11) NOT NULL
   ,"Salary"                         numeric(20,3) NOT NULL
   ,"StartDate"                      date NOT NULL
   ,"TerminationDate"                date NULL
   ,"BirthDate"                      date NULL
   ,"BenefitHealthInsurance"         bit NULL
   ,"BenefitLifeInsurance"           bit NULL
   ,"BenefitDayCare"                 bit NULL
   ,"Sex"                            char(2) NULL CONSTRAINT "Sexes" check(Sex in( 'F','M','NA' ) )
   ,CONSTRAINT "EmployeesKey" PRIMARY KEY ("EmployeeID") 
)

ALTER TABLE "GROUPO"."Employees"
    ADD CONSTRAINT "SSN" UNIQUE ( "SocialSecurityNumber" )

CREATE TABLE "GROUPO"."Departments" (
    "DepartmentID"                   int NOT NULL
   ,"DepartmentName"                 char(40) NOT NULL
   ,"DepartmentHeadID"               int NULL
   ,CONSTRAINT "DepartmentsKey" PRIMARY KEY ("DepartmentID") 
   ,CONSTRAINT "DepartmentRange" check(DepartmentID > 0 and DepartmentID <= 999)
)

The Employees table has a primary key of "EmployeeID" and a candidate key of "SocialSecurityNumber". The Departments table has a primary key of "DepartmentID". The Employees table is related to the Departments table by the definition of the foreign key:

ALTER TABLE "GROUPO"."Employees"
    ADD NOT NULL FOREIGN KEY "FK_DepartmentID_DepartmentID" ("DepartmentID")
    REFERENCES "GROUPO"."Departments" ("DepartmentID")

To find the name of a particular employee's department, there is no need to store the name of the employee's department in the Employees table. Instead, the Employees table contains a column, "DepartmentID", that holds the department number that matches one of the DepartmentID values in the Departments table.

The Employees table references the Departments table through the referential constraint above, declaring a many-to-one relationship between Employees and Departments. Moreover, this is a mandatory relationship because the foreign key column in the Employees table, DepartmentID, is declared as NOT NULL. But this is not the only relationship between the Employees and Departments tables; the Departments table itself has a foreign key to the Employees table to represent the head of each department:

ALTER TABLE "GROUPO"."Departments"
    ADD FOREIGN KEY "FK_DepartmentHeadID_EmployeeID" ("DepartmentHeadID")
    REFERENCES "GROUPO"."Employees" ("EmployeeID")
    ON DELETE SET NULL

This represents an optional many-to-one relationship between the Departments table and the Employees table; it is many-to-one because the referential constraint alone cannot prevent two or more departments having the same head. Consequently, the Employees and Departments tables form a referential cycle, with each having a foreign key to the other.

Discuss this page in DocCommentXchange.