FOREIGN KEY (REFERENCES) în PostgreSQL

coloana tip REFERENCES tabel_parinte (coloana) [ON DELETE acțiune] [ON UPDATE acțiune]
FOREIGN KEY (coloane) REFERENCES tabel_parinte (coloane) [ON DELETE acțiune]

Ce face FOREIGN KEY (REFERENCES)

O cheie străină (foreign key) garantează că o comandă nu poate aparține unui client care nu există. PostgreSQL verifică legătura la fiecare inserare sau modificare în tabelul copil și la fiecare ștergere sau modificare în tabelul părinte.

Coloana referită din părinte trebuie să aibă PRIMARY KEY sau UNIQUE. PostgreSQL nu creează automat un index pe coloana din tabelul copil; adaugă-l tu, altfel ștergerile din părinte și JOIN-urile devin lente pe tabele mari.

Acțiunea ON DELETE decide ce se întâmplă cu rândurile copil când ștergi părintele: NO ACTION / RESTRICT (implicit: eroare), CASCADE (se șterg și ele), SET NULL sau SET DEFAULT.

Parametri

Parametru	Descriere
REFERENCES tabel (coloana)	Tabelul și coloana părinte.
ON DELETE NO ACTION	Implicit. Ștergerea părintelui dă eroare dacă are copii (verificarea poate fi amânată până la final de tranzacție).
ON DELETE RESTRICT	Ca NO ACTION, dar verificarea se face imediat și nu poate fi amânată.
ON DELETE CASCADE	Șterge automat rândurile copil.
ON DELETE SET NULL	Pune NULL în coloana copilului.
DEFERRABLE INITIALLY DEFERRED	Verificarea se face la COMMIT, nu după fiecare comandă.

Ce returnează

Nimic separat: face parte din CREATE TABLE sau ALTER TABLE. Încălcările dau ERROR.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE clienti (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nume  text NOT NULL
);
INSERT INTO clienti (nume) VALUES ('Ana'), ('Mihai');

Exemplul 1: Comenzi legate de clienți

CREATE TABLE comenzi (
    id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id  integer NOT NULL REFERENCES clienti (id),
    total      numeric(10, 2) NOT NULL
);
CREATE INDEX ON comenzi (client_id);

INSERT INTO comenzi (client_id, total) VALUES (1, 149.90);
INSERT INTO comenzi (client_id, total) VALUES (99, 20);

Rezultat în psql

CREATE TABLE
CREATE INDEX
INSERT 0 1
ERROR:  insert or update on table "comenzi" violates foreign key constraint "comenzi_client_id_fkey"
DETAIL:  Key (client_id)=(99) is not present in table "clienti".

Exemplul 2: Ștergerea unui client care are comenzi

DELETE FROM clienti WHERE id = 1;

Rezultat în psql

ERROR:  update or delete on table "clienti" violates foreign key constraint "comenzi_client_id_fkey" on table "comenzi"
DETAIL:  Key (id)=(1) is still referenced from table "comenzi".

Exemplul 3: ON DELETE CASCADE pentru rândurile dependente

Liniile unei comenzi nu au sens fără comandă, deci se șterg odată cu ea.

CREATE TABLE linii_comanda (
    comanda_id  integer NOT NULL REFERENCES comenzi (id) ON DELETE CASCADE,
    produs      text NOT NULL,
    cantitate   integer NOT NULL CHECK (cantitate > 0)
);
INSERT INTO linii_comanda VALUES (1, 'Pâine', 2), (1, 'Lapte', 1);

DELETE FROM comenzi WHERE id = 1;
SELECT count(*) FROM linii_comanda;

Rezultat în psql

CREATE TABLE
INSERT 0 2
DELETE 1
 count 
-------
     0
(1 row)

Greșeli frecvente

• Uiți indexul pe coloana copil (comenzi.client_id). Fiecare ștergere din clienti scanează atunci tot tabelul comenzi.
• Pui ON DELETE CASCADE peste tot. O ștergere greșită a unui client poate șterge în lanț ani de facturi. Folosește CASCADE doar pentru date care chiar aparțin părintelui.
• Încerci să adaugi cheia străină pe un tabel cu date care nu respectă legătura: ALTER TABLE ... ADD CONSTRAINT eșuează. Curăță datele sau folosește NOT VALID, apoi VALIDATE CONSTRAINT.

Comenzi înrudite

• CREATE TABLE: Creează un tabel nou, cu coloane, tipuri de date și constrângeri: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY.
• ALTER TABLE: Modifică un tabel existent: adaugă, șterge sau redenumește coloane, schimbă tipul, valoarea implicită și constrângerile.