Egzekwowanie ograniczeń „dwie tabele dalej”

Wystąpił problem z modelowaniem schematu elektrycznego w SQL. Struktura, którą chciałbym uchwycić, to

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

gdzie „inst” jest skrótem od „instancja”.

Na przykład, mogę mieć jako partwzmacniacz pinoperacyjny LM358 z s 1OUT, 1IN-, 1IN +, GND, 2IN +, 2IN-, 2OUT i V _CC . Mógłbym wtedy umieścić tę część na schemacie, tworząc a part_insti 8 pin_insts.

Ignorując pola danych, moją pierwszą próbą schematu było

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

Głównym problemem związanym z tym schematem jest to, że pin_instmożna go powiązać part_instz, part_id=1ale pinma part_id=2.

Chciałbym uniknąć tego problemu na poziomie bazy danych niż na poziomie aplikacji. Zmodyfikowałem więc moje klucze podstawowe, aby to wymusić. Zmienione linie oznaczyłem za pomocą --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

Wadę tej metody polega na tym, że zanieczyszcza ona klucze podstawowe: gdziekolwiek się odwołuję part_inst, muszę śledzić zarówno te, jak part_inst_idi part_id. Czy istnieje inny sposób narzucenia ograniczenia pin_inst.part_inst.part_id = pin_inst.pin.part_idbez nadmiernej gadatliwości?

Minimalne rozwiązanie

Jednym radykalnym rozwiązaniem może być pin_instcałkowite usunięcie :

  part ←────────── pin
    ↑               ↑
part_inst ←───── pin_inst

W twoim pytaniu nie ma nic, co sugerowałoby, że faktycznie potrzebujesz zbędnej tabeli. Dla pins powiązanych z a part_instspójrz na pins powiązanych part.

Uprościłoby to kod do:

create table part (    -- using singular terms for table names
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part
);

^{Ale twój komentarz wyjaśnił, że nie uciekniemy od tego ...}

Alternatywa, jeśli pin_instjest potrzebna

Uwzględnienie part_idtak jak Ty to najprostsze rozwiązanie z ograniczeniami klucza obcego. Nie można odwoływać się do tabeli „dwie tabele dalej” z ograniczeniami klucza obcego .

Ale możesz przynajmniej zrobić bez „zanieczyszczania” kluczy podstawowych. Dodaj UNIQUEograniczenia .

create table part (
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, pin_id)         -- note sequence of columns
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, part_inst_id)
);
create table pin_inst (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,
    pin_id bigint not null,
    part_id bigint not,
    foreign key (part_id, pin_id) references pin,
    foreign key (part_id, part_inst_id) references part_inst
);

Na part_idpierwszym miejscu stawiam wyjątkowe ograniczenia. Nie ma to znaczenia dla integralności referencyjnej, ale ma znaczenie dla wydajności. Klucze podstawowe już implementują indeksy dla kolumn pk. Lepiej, aby druga kolumna była pierwsza w indeksach wielokolumnowych implementujących unikalne ograniczenia. Szczegóły w ramach powiązanych pytań:

• Działanie indeksów w PostgreSQL
• Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?
• Wiele do wielu i słabe podmioty

Powiązane pytania dotyczące SO:

• Czy ograniczenie UNIQUE do wielu pól może obejmować pola z więcej niż jednej tabeli?
• Ograniczenie klucza obcego z niektórymi wartościami kolumn rezydującymi w innych tabelach

Alternatywa z wyzwalaczami

Możesz skorzystać z funkcji wyzwalaczy, które są bardziej elastyczne, ale nieco bardziej skomplikowane, podatne na błędy i nieco mniej surowe. Korzyść: można zrobić bez part_inst.part_idi pin.part_id...