Dlaczego złożone klucze obce wymagają osobnego, unikalnego ograniczenia?

Oto prosta tabela, w której rekordy mogą odnosić się do rekordów nadrzędnych w tej samej tabeli:

CREATE TABLE foo (
    id         SERIAL  PRIMARY KEY,
    parent_id  INT     NULL,
    num        INT     NOT NULL,
    txt        TEXT    NULL,
    FOREIGN KEY (parent_id) REFERENCES foo(id)
);

Z dodatkowym wymogiem, że jedna z pozostałych wartości pól ( num) musi być identyczna między rekordami nadrzędnymi i podrzędnymi, pomyślałem, że złożony klucz obcy powinien załatwić sprawę. Zmieniłem ostatnią linię na

    FOREIGN KEY (parent_id, num) REFERENCES foo(id, num)

i dostał BŁĄD: nie ma unikalnego ograniczenia pasującego do podanych kluczy dla tabeli, do której istnieje odniesienie „foo” .

Mogę z łatwością dodać to ograniczenie, ale nie rozumiem, dlaczego jest to konieczne, skoro jedna z kolumn, do których istnieją odniesienia ( id), jest już gwarantowana jako niepowtarzalna? Z mojego punktu widzenia nowe ograniczenie byłoby zbędne.

To ograniczenie DBMS - we wszystkich, o ile mi wiadomo. I to nie tylko podczas dodawania kolumny, ale także podczas przestawiania kolumn. Jeśli mamy UNIQUEograniczenie (a1, a2), nie możemy go dodać FOREIGN KEY, REFERENCES (a2, a1)chyba że istnieje wyjątkowe ograniczenie, (a2, a1)które zasadniczo jest zbędne.

Dodanie tego jako funkcji nie byłoby strasznie trudne:

Gdy nie jest UNIQUEograniczeniem (a), a następnie dowolny (a, b, c, ..., z)lub (b,c, ...a, ...z)gwarantowana jest także kombinacja UNIQUE.

lub uogólnienie:

Gdy UNIQUEograniczenie jest włączone (a1, a2, ..., aN), (a1, a2, ..., aN, b1, b2, ..., bM)gwarantowana jest również dowolna kombinacja lub dowolna zmiana układu UNIQUE.

Wygląda na to, że nie został o to poproszony lub nie został uznany za wystarczająco wysoki priorytet do wdrożenia.

Zawsze możesz złożyć wniosek - na odpowiednim kanale - o wdrożenie tej funkcji. Lub nawet zaimplementuj go samodzielnie, jeśli DBMS jest oprogramowaniem typu open source, takim jak Postgres.

Klucze obce w ogólności (nie tylko złożone) MUSZĄ wskazywać na WYJĄTKOWY KLUCZ w jakimś rodzaju tabeli. Gdyby tego nie zrobili, nie byłoby integralności danych relacyjnych.

To narzeka, ponieważ podczas gdy masz unikalny klucz na (id) .. NIE masz unikalnego klucza na (id, num) .. Zatem, jeśli chodzi o DB, para (id, num) jest NIE GWARANTOWANE, aby być wyjątkowym. My, jako ludzie, możemy domyślić się, że będzie on unikalny, ale jestem pewien, że będzie musiał wprowadzić wiele dodatkowych kodów, aby Postgres był wystarczająco inteligentny, aby zobaczyć, że „och, hej .. identyfikator powinien być unikalny , więc id, num również powinno być unikalne ”..

Byłbym bardzo zaskoczony, gdyby dodali ten kod, gdy wszystko, co musisz zrobić, to stworzyć inny unikalny indeks na dwóch kolumnach, aby rozwiązać problem.

Żeby było jasne, kod, który musieliby dodać, nie byłby zwykłym przypadkiem ... musiałby obsłużyć wszystkie przypadki, nawet te, w których klucz obcy jest na 4+ kolumnach itp. Jestem pewien logika byłaby dość złożona.