Database-Design
有循環外鍵引用是否可以接受如何避免它們?
在外鍵欄位的兩個表之間進行循環引用是否可以接受?
如果沒有,如何避免這些情況?
如果是這樣,如何插入數據?
以下是(在我看來)可以接受循環引用的範例:
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;
這提供了以下能力:
- 按照 Pieter 在他的回答中推薦的方式,通過交叉引用表清楚地描述聯繫人和客戶之間的關係
- 以健全、非循環的方式保持參照完整性。
- 通過索引提供高度可維護的主要聯繫人列表。
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;