Database-Design

有循環外鍵引用是否可以接受如何避免它們?

  • August 10, 2018

在外鍵欄位的兩個表之間進行循環引用是否可以接受?

如果沒有,如何避免這些情況?

如果是這樣,如何插入數據?

以下是(在我看來)可以接受循環引用的範例:

CREATE TABLE Account
(
   ID INT PRIMARY KEY IDENTITY,
   Name VARCHAR(50)
)

CREATE TABLE Contact
(
   ID INT PRIMARY KEY IDENTITY,
   Name VARCHAR(50),
   AccountID INT FOREIGN KEY REFERENCES Account(ID)
)

ALTER TABLE Account ADD PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)

由於您為外鍵使用可為空的欄位,因此您實際上可以建構一個按照您設想的方式正常工作的系統。為了將行插入到 Accounts 表中,您需要在 Contacts 表中存在一行,除非您允許使用 null PrimaryContactID 插入到 Accounts 中。為了在沒有 Account 行的情況下創建聯繫人行,您必須允許 Contacts 表中的 AccountID 列可以為空。這允許帳戶沒有聯繫人,並允許聯繫人沒有帳戶。也許這是可取的,也許不是。

話雖如此,我個人的偏好是具有以下設置:

CREATE TABLE dbo.Accounts
(
   AccountID INT NOT NULL
       CONSTRAINT PK_Accounts
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , AccountName VARCHAR(255)
);

CREATE TABLE dbo.Contacts
(
   ContactID INT NOT NULL
       CONSTRAINT PK_Contacts
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , ContactName VARCHAR(255)
);

CREATE TABLE dbo.AccountsContactsXRef
(
   AccountsContactsXRefID INT NOT NULL
       CONSTRAINT PK_AccountsContactsXRef
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , AccountID INT NOT NULL
       CONSTRAINT FK_AccountsContactsXRef_AccountID
       FOREIGN KEY REFERENCES dbo.Accounts(AccountID)
   , ContactID INT NOT NULL
       CONSTRAINT FK_AccountsContactsXRef_ContactID
       FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
   , IsPrimary BIT NOT NULL 
       CONSTRAINT DF_AccountsContactsXRef
       DEFAULT ((0))
   , CONSTRAINT UQ_AccountsContactsXRef_AccountIDContactID
       UNIQUE (AccountID, ContactID)
);

CREATE UNIQUE INDEX IX_AccountsContactsXRef_Primary
ON dbo.AccountsContactsXRef(AccountID, IsPrimary)
WHERE IsPrimary = 1;

這提供了以下能力:

  1. 按照 Pieter 在他的回答中推薦的方式,通過交叉引用表清楚地描述聯繫人和客戶之間的關係
  2. 以健全、非循環的方式保持參照完整性。
  3. 通過索引提供高度可維護的主要聯繫人列表。IX_AccountsContactsXRef_Primary該索引包含一個過濾器,因此它僅適用於支持它們的平台。由於該索引是使用UNIQUE選項指定的,因此每個帳戶只能有一個主要聯繫人。

例如,如果您想顯示所有聯繫人的列表,其中有一列表示“主要”狀態,在每個帳戶的列表頂部顯示主要聯繫人,您可以執行以下操作:

SELECT A.AccountName
   , C.ContactName
   , XR.IsPrimary
FROM dbo.Accounts A
   INNER JOIN dbo.AccountsContactsXRef XR ON A.AccountID = XR.AccountID
   INNER JOIN dbo.Contacts C ON XR.ContactID = C.ContactID
ORDER BY A.AccountName
   , XR.IsPrimary DESC
   , C.ContactName;

過濾索引可防止每個帳戶插入多個主要聯繫人,同時提供返回主要聯繫人列表的快速方法。可以很容易地想像另一列,IsActive使用非唯一過濾索引來維護每個帳戶的聯繫人歷史記錄,即使在該聯繫人不再與該帳戶關聯之後:

ALTER TABLE dbo.AccountsContactsXRef
ADD IsActive BIT NOT NULL
CONSTRAINT DF_AccountsContactsXRef_IsActive
DEFAULT ((1));

CREATE INDEX IX_AccountsContactsXRef_IsActive
ON dbo.AccountsContactsXRef(IsActive)
WHERE IsActive = 1;

引用自:https://dba.stackexchange.com/questions/102903