Diciamo che vogliamo far rispettare che una tabella rappresenta un albero. Ho trovato questo metodo per farlo:
create table tree (
id integer[] primary key,
parent integer[] references tree (id),
item text not null,
constraint tree_parent check (
(id = array[]::integer[] and parent is null)
or parent = id[1:array_length(id,1)-1])
);
Un nodo nell'albero è identificato dal suo percorso dal nodo radice. In quanto tale, la chiave esterna parent è solo quel percorso con un elemento lasciato cadere alla fine. La combinazione di foreign key e check garantisce la struttura ad albero.
Prima domanda: questa è una buona idea? Ci sono approcci migliori? In Oracle, almeno, sono in grado di definire parent come una colonna virtuale, che è elegante.
Seconda domanda: esiste un nome per questo approccio? Mi piace pensare a questo come a una "chiave esterna funzionale": tree.id riferimenti tree.id , ma tramite una funzione che rilascia l'ultimo elemento nel percorso. Penso che questo concetto abbia un uso più generale rispetto all'esempio dell'albero sopra.