Klucz obcy wiąże kolumnę z kluczem głównym albo unikalną kolumną innej tabeli. PostgreSQL odrzuca każdą wartość, dla której nie ma tam pasującego wiersza. Zapisujesz go jako column type REFERENCES other_table (column). Dodaj ON DELETE CASCADE albo ON DELETE SET NULL, żeby określić, co dzieje się z wierszami podrzędnymi po usunięciu nadrzędnego.
Kolumna, do której się odwołujesz, musi mieć ograniczenie PRIMARY KEY albo UNIQUE. Przy REFERENCES customers bez kolumny PostgreSQL używa klucza głównego customers.
Jak wygląda naruszenie
Zamówienie dla klienta, który nie istnieje, zostaje odrzucone:
Błąd podaje nazwę ograniczenia, orders_customer_id_fkey, czyli nazwę, którą PostgreSQL nadaje kluczowi obcemu bez nazwy. Usunięcie klienta, który ma jeszcze zamówienia, kończy się takim samym błędem, chyba że klucz mówi inaczej.
Opcje ON DELETE
| Opcja | Gdy wiersz nadrzędny zostaje usunięty |
|---|---|
NO ACTION (domyślnie) | Usunięcie się nie udaje, dopóki istnieją wiersze podrzędne |
RESTRICT | To samo, ale sprawdzenia nie można odłożyć do końca transakcji |
CASCADE | Wiersze podrzędne też są usuwane |
SET NULL | Kolumna klucza obcego w wierszu podrzędnym staje się NULL |
SET DEFAULT | Kolumna w wierszu podrzędnym dostaje wartość domyślną |
ON UPDATE przyjmuje te same opcje. Działa, gdy zmienia się wartość klucza w wierszu nadrzędnym, co przy generowanych identyfikatorach zdarza się rzadko.
CASCADE pasuje do wierszy, które bez rodzica nie mają sensu, na przykład pozycji zamówienia:
Zostaje tylko chair. Kaskady idą dalej po kolejnych kluczach, więc jedno usunięcie może skasować wiersze w kilku tabelach.
SET NULL zostawia wiersz podrzędny i odłącza go:
Ada zostaje, a team_id ma wartość NULL. Od PostgreSQL 15 ON DELETE SET NULL (column) może wyczyścić tylko część kolumn klucza wielokolumnowego.
Dodanie klucza obcego do istniejącej tabeli
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
To sprawdza każdy istniejący wiersz i kończy się błędem, jeśli któryś nie ma rodzica. Przy dużej tabeli dodaj klucz z NOT VALID, wtedy sprawdzane są tylko nowe wiersze. Potem uruchom ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;, żeby sprawdzić stare wiersze bez blokowania zapisów. Żeby zmienić akcję ON DELETE istniejącego klucza, usuń ograniczenie i dodaj je ponownie w jednym ALTER TABLE.
Indeks na kolumnie klucza obcego
PostgreSQL indeksuje stronę, do której się odwołujesz, a nie kolumnę odwołującą się. Bez indeksu na orders.customer_id każde usunięcie z customers przeszukuje całe orders w poszukiwaniu wierszy podrzędnych, podobnie jak pobranie zamówień jednego klienta:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Żeby wypisać klucze obce tabeli, odpytaj pg_constraint:
Złączenia po tych kluczach omawia strona o złączeniach, a rozdział o ograniczeniach w dokumentacji zawiera pełne reguły, także dla kluczy odraczanych.
Najczęściej zadawane pytania
Czy PostgreSQL tworzy indeks dla klucza obcego?
Nie na kolumnie odwołującej się. Kolumna, do której się odwołujesz, już go ma, bo musi być kluczem głównym albo unikalna. Utwórz indeks na kolumnie klucza obcego sam, bo złączenia i usuwanie z tabeli nadrzędnej ją przeszukują.
Jak usunąć klucz obcy w PostgreSQL?
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Klucze obce bez nazwy nazywają się table_column_fkey; \d orders w psql pokazuje dokładną nazwę.
Czy kolumna klucza obcego może być NULL w PostgreSQL?
Tak. NULL w kolumnie klucza obcego nie jest sprawdzany, więc wiersz nie potrzebuje rodzica. Dodaj NOT NULL, gdy każdy wiersz musi go mieć.
Czy klucz obcy może odwoływać się do tej samej tabeli?
Tak. Kolumna taka jak manager_id int REFERENCES employees (id) wskazuje na inny wiersz tej samej tabeli. Tak zapisuje się drzewa, na przykład strukturę organizacyjną albo wątki komentarzy.