引用循环

引用表和被引用表不必互不相同。表可以包含一个引用自身的外键。将此类表称为自引用表。自引用表是引用循环的一种特殊情况。

示例 1

SQL Anywhere 示例数据库有一个保存雇员信息的表和一个保存部门信息的表：

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)
)

Employees 表具有主键 "EmployeeID" 和候选键 "SocialSecurityNumber"。Departments 表具有主键 "DepartmentID"。Employees 表通过外键的定义与 Departments 表关联：

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

要查找特定雇员所在部门的名称，不需要将该雇员的部门名称存储到 Employees 表中。而是在 Employees 表中包含一个 "DepartmentID" 列，用以保存与 Departments 表中某一 DepartmentID 值相匹配的部门编号。

Employees 表通过上述引用约束来引用 Departments 表，声明 Employees 与 Departments 之间具有多对一关系。而且这是一种强制关系，因为 Employees 表中的外键列 DepartmentID 被声明为 NOT NULL。但这并不是 Employees 表与 Departments 表之间的唯一关系；Departments 表本身具有一个指向 Employees 表的外键，用于表示各部门的负责人：

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

这表示 Departments 表与 Employees 表之间存在可选的多对一关系；这的确是多对一关系，因为引用约束本身不能阻止出现两个或多个部门具有相同负责人的现象。因此，Employees 表和 Departments 表形成了一个引用循环，每个表都有一个指向对方的外键。

使用DocCommentXchange讨论此页。