参照循環性

参照元テーブルと参照先テーブルは別である必要はありません。テーブルに、それ自体を参照する外部キーを含めることができます。これを自己参照テーブルと呼びます。自己参照テーブルは、参照循環性の特殊な場合です。

例 1

SQL Anywhere サンプルデータベースには、従業員 (employee) 情報が入ったテーブルが 1 つと、部署 (department) 情報の入ったテーブルが 1 つあります。

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 テーブルには、Departments テーブルの DepartmentID 値の 1 つと一致する部署番号の入った "DepartmentID" カラムがあります。

Employees テーブルは、Employees と Departments 間の多対 1 の関係を宣言する上記の参照整合性制約によって、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 テーブル間のオプションの多対 1 の関係です。参照整合性制約だけでは複数の部署の長が同じになることを回避できないため、多対 1 になっています。その結果、Employees テーブルと Departments テーブルは参照循環性を形成し、それぞれに他方への外部キーがあります。

DocCommentXchange で意見交換できます