Una chiave esterna lega una colonna alla chiave primaria, o a una colonna univoca, di un'altra tabella. PostgreSQL rifiuta qualsiasi valore che lì non abbia una riga corrispondente. Si scrive column type REFERENCES other_table (column). Aggiungi ON DELETE CASCADE o ON DELETE SET NULL per dire cosa succede alle righe figlie quando il padre viene eliminato.
La colonna referenziata deve avere un vincolo PRIMARY KEY o UNIQUE. Con REFERENCES customers senza colonna, PostgreSQL usa la chiave primaria di customers.
Come si presenta una violazione
Un ordine per un cliente che non esiste viene rifiutato:
L'errore nomina il vincolo, orders_customer_id_fkey, che è il nome che PostgreSQL dà a una chiave esterna senza nome. Eliminare un cliente che ha ancora ordini fallisce allo stesso modo, a meno che la chiave non dica altrimenti.
Le opzioni di ON DELETE
| Opzione | Quando la riga padre viene eliminata |
|---|---|
NO ACTION (predefinita) | L'eliminazione fallisce finché esistono righe figlie |
RESTRICT | Uguale, ma il controllo non può essere rimandato alla fine della transazione |
CASCADE | Vengono eliminate anche le righe figlie |
SET NULL | La colonna della chiave esterna nella riga figlia diventa NULL |
SET DEFAULT | La colonna della riga figlia prende il suo valore predefinito |
ON UPDATE accetta le stesse opzioni. Si applica quando cambia il valore della chiave del padre, cosa rara con gli id generati.
CASCADE va bene per le righe che non hanno senso senza il loro padre, come le voci di un ordine:
Resta solo chair. Le cascate seguono altre chiavi, quindi una sola eliminazione può rimuovere righe in più tabelle.
SET NULL tiene la riga figlia e la stacca dal padre:
Ada resta, con team_id impostato a NULL. Da PostgreSQL 15, ON DELETE SET NULL (column) può azzerare solo alcune colonne di una chiave su più colonne.
Aggiungere una chiave esterna a una tabella esistente
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
Questo controlla ogni riga esistente e fallisce se una non ha un padre. Su una tabella grande, aggiungi la chiave con NOT VALID, che controlla solo le righe nuove. Poi esegui ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; per controllare le righe vecchie senza bloccare le scritture. Per cambiare l'azione ON DELETE di una chiave esistente, elimina il vincolo e aggiungilo di nuovo in un solo ALTER TABLE.
Indicizzare la colonna della chiave esterna
PostgreSQL indicizza il lato referenziato, non la colonna che fa riferimento. Senza un indice su orders.customer_id, ogni eliminazione da customers scansiona tutta orders per cercare le righe figlie, e lo stesso vale per leggere gli ordini di un cliente:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Per elencare le chiavi esterne di una tabella, interroga pg_constraint:
I join lungo queste chiavi sono spiegati nella pagina sui join, e il capitolo sui vincoli del manuale ha le regole complete, comprese le chiavi differibili.
Domande frequenti
PostgreSQL crea un indice per una chiave esterna?
Non sulla colonna che fa riferimento. La colonna referenziata ne ha già uno, perché deve essere una chiave primaria o univoca. Crea tu un indice sulla colonna della chiave esterna, perché i join e le eliminazioni dalla tabella padre la cercano.
Come elimino una chiave esterna in PostgreSQL?
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Le chiavi esterne senza nome si chiamano table_column_fkey; \d orders in psql mostra il nome esatto.
Una colonna con chiave esterna può essere NULL in PostgreSQL?
Sì. Un NULL nella colonna della chiave esterna non viene controllato, quindi la riga non ha bisogno di un padre. Aggiungi NOT NULL quando ogni riga deve averne uno.
Una chiave esterna può fare riferimento alla stessa tabella?
Sì. Una colonna come manager_id int REFERENCES employees (id) punta a un'altra riga della stessa tabella. È così che si salvano strutture ad albero come organigrammi e thread di commenti.