Czy dopuszczalne są cykliczne odwołania do kluczy obcych \ Jak ich uniknąć?

Czy dopuszczalne jest okrągłe odniesienie między dwiema tabelami w polu klucza obcego?

Jeśli nie, jak można uniknąć takich sytuacji?

Jeśli tak, w jaki sposób można wstawić dane?

Poniżej znajduje się przykład, w którym (moim zdaniem) dopuszczalne byłoby użycie okólnika:

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)

Ponieważ używasz pól zerowalnych dla kluczy obcych, możesz w rzeczywistości zbudować system, który działa poprawnie tak, jak go sobie wyobrażasz. Aby wstawić wiersze do tabeli Konta, musisz mieć wiersz w tabeli Kontakty, chyba że zezwalasz na wstawianie do kont z zerowym PrimaryContactID. Aby utworzyć wiersz kontaktu bez obecnego wiersza konta, musisz zezwolić, aby kolumna AccountID w tabeli Kontakty miała wartość null. Dzięki temu Konta nie mają kontaktów, a Kontakty nie mają konta. Być może jest to pożądane, a może nie.

Powiedziawszy to, moim osobistym wyborem będzie mieć następującą konfigurację:

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;

Zapewnia to możliwość:

1. Wyraźnie nakreśl relacje między kontaktami i kontami za pomocą tabeli odsyłaczy, tak jak Pieter zaleca w swojej odpowiedzi
2. Zachowaj integralność referencyjną w solidny, nieokrągły sposób.
3. Zapewnij wysoce utrzymywalną listę głównych kontaktów za pośrednictwem IX_AccountsContactsXRef_Primaryindeksu. Ten indeks zawiera filtr, więc będzie działał tylko na platformach, które je obsługują. Ponieważ ten indeks jest określony z UNIQUEopcją, dla każdego konta może istnieć tylko jeden główny kontakt.

Na przykład, jeśli chcesz wyświetlić listę wszystkich kontaktów z kolumną oznaczającą status „podstawowy”, pokazującą główne kontakty na górze listy dla każdego konta, możesz:

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;

Filtrowany indeks zapobiega wstawianiu więcej niż jednego głównego kontaktu na konto, a jednocześnie zapewnia szybką metodę zwrotu listy głównych kontaktów. Można łatwo wyobrazić sobie inną kolumnę IsActivez nieunikalnym przefiltrowanym indeksem do przechowywania historii kontaktów na konto, nawet jeśli kontakt nie jest już powiązany z kontem:

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;

Nie, niedopuszczalne są okrągłe odwołania do kluczy obcych. Nie tylko dlatego, że wstawianie danych byłoby niemożliwe bez ciągłego upuszczania i odtwarzania ograniczeń. ale ponieważ jest to zasadniczo wadliwy model dowolnej dziedziny, o której mogę myśleć. W twoim przykładzie nie mogę wymyślić żadnej domeny, w której relacja między kontem a kontaktem nie jest NN, wymagająca tabeli połączeń z referencjami FK z powrotem do konta i kontaktu.

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

Twój obiekt zewnętrzny może wskazywać na główny kontakt, a nie na konto. Twoje dane wyglądałyby tak:

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