Jak powiązać dwa wiersze w tej samej tabeli

Mam tabelę, w której wiersze mogą być ze sobą powiązane, i logicznie związek przebiega w obie strony (w zasadzie jest bezkierunkowy) między dwoma rzędami. (A jeśli zastanawiasz się, tak, to naprawdę powinien to być jeden stół. Są to dwie rzeczy dokładnie tego samego logicznego bytu / typu). Mogę wymyślić kilka sposobów na przedstawienie tego:

1. Przechowuj relację i jej odwrotność
2. Przechowuj relację w jeden sposób, ogranicz przechowywanie bazy danych w inny sposób i miej dwa indeksy o przeciwnych zamówieniach dla FK (jeden indeks to indeks PK)
3. Zapisz relację w jedną stronę za pomocą dwóch indeksów i pozwól, by i tak wstawić drugą (brzmi trochę pechowo, ale hej, kompletność)
4. Utwórz jakiś stół grupujący i umieść na nim FK na oryginalnym stole. (Podnosi mnóstwo pytań. Tabela grupowania miałaby tylko liczbę; dlaczego w ogóle miałaby tabelę? Uczynić FK NULLable lub mieć grupy z jednym wierszem powiązane?)

Jakie są główne zalety i wady tych sposobów i oczywiście jest jakiś sposób, o którym nie myślałem?

Oto SQLFiddle do zabawy: http://sqlfiddle.com/#!12/7ee1a/1/0 . (Zdarza się, że jest PostgreSQL, ponieważ tego właśnie używam, ale nie sądzę, że to pytanie jest bardzo specyficzne dla PostgreSQL.) Obecnie przechowuje zarówno relację, jak i jej odwrotność, jako przykład.

To, co zaprojektowałeś, jest dobre. To, co należy dodać, to ograniczenie uniemożliwiające ukierunkowanie związku. Nie możesz więc mieć (1,5)wiersza bez (5,1)dodanego wiersza.

Można to osiągnąć ^* za pomocą ograniczenia do odwoływania się do tabeli mostu.

*: _{można tego dokonać w Postgres, Oracle, DB2 i wszystkich DBMS, które zaimplementowały ograniczenia klucza obcego zgodnie z opisem standardu SQL (odroczone, np. sprawdzone na końcu transakcji). Odroczone sprawdzanie i tak nie jest tak naprawdę potrzebne, jak w SQL- Serwer, który sprawdza je na końcu instrukcji i ta konstrukcja nadal działa. Nie możesz tego zrobić w MySQL, ponieważ „InnoDB sprawdza rzędy po rzędzie UNIQUE i OBCYCH KLUCZOWYCH ograniczeń” .}

Tak więc w Postgres następujące wymagania będą pasować do twoich wymagań:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Testowany w: SQL-Fiddle

Jeśli spróbujesz dodać wiersz (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

Nie działa z:

BŁĄD: wstaw lub aktualizuj w tabeli „bridge_x” narusza ograniczenie klucza obcego „x_x_directionless”
Szczegół: Klucz (x_id2, x_id1) = (5, 1) nie występuje w tabeli „bridge_x” .:
WSTAWIĆ DO WARTOŚCI bridge_x (1,5)

Dodatkowo możesz dodać CHECKograniczenie, jeśli chcesz zabronić (y,y)wierszy:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

Istnieją inne sposoby realizacji tego, jak wspomniałeś, na przykład przechowywanie tylko jednego kierunku relacji (w jednym wierszu, a nie dwóch) przez wymuszenie niższego identyfikatora x_id1i wyższego identyfikatora w x_id2kolumnie. Wygląda na łatwiejsze do wdrożenia, ale zwykle prowadzi do bardziej złożonych zapytań później:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);