主页 / dba / 问题 / 102903
Accepted
KidCode
Asked: 2015-06-01 09:00:17 +0800 CST

有循环外键引用是否可以接受\如何避免它们？

  • 772

在外键字段的两个表之间进行循环引用是否可以接受？

如果没有，如何避免这些情况？

如果是这样，如何插入数据？

以下是（在我看来）可以接受循环引用的示例：

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)
database-design foreign-key

3 个回答

  1. Best Answer

    由于您为外键使用可为空的字段，因此您实际上可以构建一个按照您设想的方式正常工作的系统。为了将行插入到 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;
    • 20

  2. 不，循环外键引用是不可接受的。不仅因为如果不不断删除和重新创建约束就不可能插入数据。但因为它是我能想到的任何领域的根本缺陷模型。在您的示例中，我想不出Account 和Contact 之间的关系不是NN 的任何域，需要一个带有FK 引用的联结表返回Account 和Contact。

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

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

CREATE TABLE AccountContact
(
    AccountID INT FOREIGN KEY REFERENCES Account(ID),
    ContactID INT FOREIGN KEY REFERENCES Contact(ID),

    primary key(AccountID,ContactID)
)
    • 8

  3. 您可以让您的外部对象指向主要联系人，而不是客户。您的数据将如下所示：

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

CREATE TABLE AccountOwner (
    Other Stuff Here . . .
    PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)
)
    • 1

相关问题