Implementacja relacji wiele do wielu z ograniczeniami całkowitego uczestnictwa w SQL

Jak zaimplementować w SQL scenariusz przedstawiony na poniższym diagramie relacji jednostka?

Jak pokazano, każde Awystąpienie typu jednostki musi być powiązane z co najmniej jednym B odpowiednikiem (wskazanym przez podwójne linie łączące) i odwrotnie . Wiem, że powinienem utworzyć trzy poniższe tabele:

    CREATE TABLE A
    (
        a INT NOT NULL,
        CONSTRAINT A_PK PRIMARY KEY (a)
    );

    CREATE TABLE B
    (
        b INT NOT NULL,
        CONSTRAINT B_PK PRIMARY KEY (b)
    );

    CREATE TABLE R
    (
        a INT NOT NULL,
        b INT NOT NULL,
        CONSTRAINT R_PK      PRIMARY KEY (a, b),
        CONSTRAINT R_to_A_FK FOREIGN KEY (a)
            REFERENCES A (a),
        CONSTRAINT R_to_B_FK FOREIGN KEY (b)
            REFERENCES B (b)
    );

Ale co z wdrożeniem całkowitych ograniczeń uczestnictwa (tj. Egzekwowaniem, że każde wystąpienie jednego Alub jednego z nich Bjest zaangażowane w co najmniej jedno wystąpienie relacji z drugim)?

Nie jest to łatwe w SQL, ale nie jest niemożliwe. Jeśli chcesz DEFERRABLEwymusić to przez sam DDL, DBMS musi mieć zaimplementowane ograniczenia. Można to zrobić (i można sprawdzić, czy działa w Postgres, który je zaimplementował):

-- lets create first the 2 tables, A and B:
CREATE TABLE a 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid) 
 );

CREATE TABLE b 
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid) 
 );

-- then table R:
CREATE TABLE r 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,  
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

Tutaj jest „normalny” projekt, w którym każdy Amoże być powiązany z zerem, jednym lub wieloma Bi każdy Bmoże być powiązany z zerem, jednym lub wieloma A.

„Całkowity udział” ograniczenie wymaga ograniczenia w odwrotnej kolejności (od Ai B, odpowiednio, odniesienie R). Uwzględniając FOREIGN KEYograniczenia w przeciwnych kierunkach (od X do Y i od Y do X) tworząc krąg (A „jajka i kury” problem) i dlatego potrzebny jest jeden z nich przynajmniej bycia DEFERRABLE. W tym przypadku mamy dwa koła ( A -> R -> Aa B -> R -> Bwięc potrzebujemy dwóch odroczonych ograniczeń:

-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
  ADD CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

ALTER TABLE b
  ADD CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

Następnie możemy przetestować, czy możemy wstawić dane. Pamiętaj, że INITIALLY DEFERREDnie jest to konieczne. Moglibyśmy zdefiniować ograniczenia jako, DEFERRABLE INITIALLY IMMEDIATEale wtedy musielibyśmy użyć SET CONSTRAINTSinstrukcji, aby odroczyć je podczas transakcji. W każdym przypadku musimy wstawić do tabel w ramach jednej transakcji:

-- insert data 
BEGIN TRANSACTION ;
    INSERT INTO a (aid, bid)
    VALUES
      (1, 1),    (2, 5),
      (3, 7),    (4, 1) ;

    INSERT INTO b (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7) ;

    INSERT INTO r (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7),    (4, 1),
      (4, 2),    (4, 7) ; 
 END ;

Testowany w SQLfiddle .

Jeśli DBMS nie ma DEFERRABLEograniczeń, jednym z obejść jest zdefiniowanie kolumn A (bid)i B (aid)jako NULL. Te INSERTprocedury / sprawozdanie następnie trzeba najpierw włożyć w Aa B(wprowadzenie null w bidi aid, odpowiednio), a następnie wstawić Ra następnie aktualizuje wartości zerowe powyżej odpowiednich wartości niezerowy z R.

Dzięki takiemu podejściu, DBMS nie wymusza wymagań przez DDL sam ale każdy INSERT(a UPDATE, a DELETE, a MERGE) procedura musi być rozważone i odpowiednio dostosowane i użytkownicy muszą być ograniczone do korzystania tylko im i nie mają bezpośredniego dostępu do zapisu tabel.

Posiadanie kręgów w FOREIGN KEYograniczeniach nie jest uważane przez wiele najlepszych praktyk i nie bez powodu, złożoność jest jedną z nich. Na przykład przy drugim podejściu (z kolumnami zerowalnymi) aktualizacja i usuwanie wierszy będzie musiała zostać wykonana z dodatkowym kodem, w zależności od DBMS. Na przykład w SQL Server nie można po prostu umieścić, ON DELETE CASCADEponieważ kaskadowe aktualizacje i usuwanie nie są dozwolone, gdy istnieją kręgi FK.

Przeczytaj także odpowiedzi na powiązane pytanie:
Jak utrzymywać relację jeden do wielu z uprzywilejowanym dzieckiem?

Innym trzecim podejściem (patrz moja odpowiedź w wyżej wspomnianym pytaniu) jest całkowite usunięcie okrągłych FK. Więc, utrzymując pierwszą część kodu (ze stołami A, B, Ri kluczy obcych tylko z R na A i B) w niemal nienaruszonym stanie (w rzeczywistości uproszczeniu), dodajemy kolejny tabela Ado przechowywania „musi mieć jeden” Pozycja z B. Tak więc A (bid)kolumna przesuwa się na A_one (bid)To samo dzieje się w przypadku odwrotnej relacji z B do A:

CREATE TABLE a 
( aid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid) 
 );

CREATE TABLE b 
( bid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid) 
 );

-- then table R:
CREATE TABLE r 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,  
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

CREATE TABLE a_one 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_one_pk PRIMARY KEY (aid),
  CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

CREATE TABLE b_one
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_one_pk PRIMARY KEY (bid),
  CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

Różnica w stosunku do pierwszego i drugiego podejścia polega na tym, że nie ma okrągłych FK, więc kaskadowe aktualizacje i usuwanie będą działać dobrze. Egzekwowanie „całkowitego udziału” nie odbywa się wyłącznie przez DDL, jak w drugim podejściu i musi być wykonane za pomocą odpowiednich procedur ( INSERT/UPDATE/DELETE/MERGE). Niewielka różnica w drugim podejściu polega na tym, że wszystkie kolumny można zdefiniować jako wartości zerowe.

Innym czwartym podejściem (patrz odpowiedź @Aarona Bertranda w wyżej wspomnianym pytaniu) jest użycie filtrowanych / częściowych indeksów unikalnych, jeśli są one dostępne w twoim DBMS (w Rtym przypadku potrzebujesz dwóch z nich w tabeli). Jest to bardzo podobne do trzeciego podejścia, z tym wyjątkiem, że nie będziesz potrzebował 2 dodatkowych stołów. Ograniczenie „całkowitego uczestnictwa” musi być nadal stosowane przez kod.

Nie możesz bezpośrednio. Na początek nie można wstawić rekordu dla A bez istniejącego już B, ale nie można utworzyć rekordu B, jeśli nie ma dla niego rekordu A. Istnieje kilka sposobów egzekwowania go za pomocą takich elementów, jak wyzwalacze - musiałbyś sprawdzić każdą wstawkę i usunąć, że przynajmniej jeden odpowiedni rekord pozostaje w tabeli łączy AB.