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


29

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)

2
Jeśli tak, to w jaki sposób można wstawić dane ” - zależy od używanego DBMS. Na przykład Postgres, Oracle, SQLite i Apache Derby pozwalają na odłożenie ograniczeń, które by to umożliwiły. W przypadku innych DBMS nie masz szczęścia (ale nadal kwestionowałbym potrzebę takiego ograniczenia)
a_horse_w_na_nazwa

Odpowiedzi:


12

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;

1
czy powiedziałbyś ogólnie, że należy unikać okólników? Uważam, że nie są one złe i wykorzystały je do realizacji skutecznych projektów. Sprawiają, że usuwanie jest nieco bardziej skomplikowane, ponieważ wymagają i aktualizują się do wartości NULL w innym przypadku, gdyby nie był podmiotem dominującym, ale uważam, że jest to niska cena za wygodę. Używam ich w Postgres, gdzie pole FK jest zerowalne, więc tworzę z nim wiersz NULL, a następnie aktualizuję pole FK do PK z tabeli potomnej, aby właściwie wykonać tę samą funkcję, jak opisano w OP
amfibia

Nie lubię okólników, ponieważ po prostu niepotrzebnie komplikują projekt i przez większość czasu nie oferują żadnej znaczącej korzyści w zakresie wydajności wartej kompromisu. Jestem fanem brzytwy Ockhama, w wyniku czego dążę do najprostszego rozwiązania danego problemu.
Max Vernon

1
Jestem zwolennikiem brzytwy Ockhama, ale wyżej opisany projekt pozwolił mi uniknąć niektórych drugich zapytań lub połączeń, niekoniecznie naruszając trzecią normalną formę. Doceniam twoją opinię
amfibia

6

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

5
wstawienie danych byłoby niemożliwe ” - nie, nie byłoby niemożliwe. Po prostu zadeklaruj ograniczenia jako możliwe do odroczenia. Ale zgadzam się: w prawie wszystkich przypadkach odnośniki kołowe są złym projektem.
a_horse_w_no_name

3
@ a_horse - nie jest możliwe zdefiniowanie odroczonego odwołania w SQL Server ... Wiem, że możesz w Oracle, chciałem tylko wskazać na rozbieżność.
Max Vernon

2
@MaxVernon: pytanie dotyczy nie tylko programu SQL Server i istnieje więcej DBMS niż tylko Oracle, które obsługują odroczone ograniczenia - ale, jak powiedziałem: zgadzam się z Pieter, że sam projekt jest zły (a jego rozwiązanie ma znacznie więcej sensu)
a_horse_w_no_name

4
Pomijając specyfikę dowolnego przykładu, ogólnie rzecz biorąc, nie ma nic niekoniecznie złego lub „wadliwego” w posiadaniu wzajemnych (tj. „Okrągłych”) ograniczeń integralności referencyjnej. Jest to w efekcie tylko przykład zależności zależności. Zależności łączenia są dobrą rzeczą, jeśli DBMS pozwala je zaimplementować. Tyle, że w SQL DBMS implementacja złożonych zależności między tabelami nie jest łatwa.
nvogel

6
@Pieter, 1-1 nie jest jedynym przykładem zależności łączenia, a nawet nie jest to szczególny przypadek. Są przypadki, w których ograniczenia zależności łączenia mają doskonały sens.
nvogel,

1

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)
)
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.