Una clave foránea une una columna con la clave primaria, o una columna única, de otra tabla. PostgreSQL rechaza cualquier valor que no tenga una fila correspondiente allí. Se escribe como column type REFERENCES other_table (column). Añade ON DELETE CASCADE u ON DELETE SET NULL para indicar qué pasa con las filas hijas cuando se borra el padre.
La columna referenciada debe tener una restricción PRIMARY KEY o UNIQUE. Con REFERENCES customers sin columna, PostgreSQL usa la clave primaria de customers.
Cómo se ve una violación
Un pedido para un cliente que no existe se rechaza:
El error nombra la restricción, orders_customer_id_fkey, que es el nombre que PostgreSQL da a una clave foránea sin nombre. Borrar un cliente que todavía tiene pedidos falla de la misma forma, salvo que la clave diga otra cosa.
Opciones de ON DELETE
| Opción | Cuando se borra la fila padre |
|---|---|
NO ACTION (por defecto) | El borrado falla mientras existan filas hijas |
RESTRICT | Igual, pero la comprobación no se puede aplazar al final de la transacción |
CASCADE | Las filas hijas también se borran |
SET NULL | La columna de clave foránea del hijo pasa a NULL |
SET DEFAULT | La columna del hijo recibe su valor por defecto |
ON UPDATE acepta las mismas opciones. Se aplica cuando cambia el valor de la clave del padre, algo raro con ids generados.
CASCADE encaja con filas que no tienen sentido sin su padre, como las líneas de un pedido:
Solo queda chair. Las cascadas siguen otras claves, así que un solo borrado puede eliminar filas en varias tablas.
SET NULL conserva la fila hija y la desvincula:
Ada se queda, con team_id en NULL. Desde PostgreSQL 15, ON DELETE SET NULL (column) puede vaciar solo algunas columnas de una clave de varias columnas.
Añadir una clave foránea a una tabla existente
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
Esto comprueba todas las filas existentes y falla si alguna no tiene padre. En una tabla grande, añade la clave con NOT VALID, que solo comprueba las filas nuevas. Después ejecuta ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; para revisar las filas antiguas sin bloquear las escrituras. Para cambiar la acción ON DELETE de una clave existente, elimina la restricción y vuelve a añadirla en un mismo ALTER TABLE.
Indexar la columna de la clave foránea
PostgreSQL indexa el lado referenciado, no la columna que referencia. Sin un índice en orders.customer_id, cada borrado en customers recorre toda la tabla orders buscando hijos, y lo mismo ocurre al traer los pedidos de un cliente:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Para listar las claves foráneas de una tabla, consulta pg_constraint:
Los joins a través de estas claves se explican en la página de joins, y el capítulo de restricciones del manual tiene todas las reglas, incluidas las claves aplazables.
Preguntas frecuentes
¿PostgreSQL crea un índice para una clave foránea?
No en la columna que referencia. La columna referenciada ya tiene uno, porque debe ser clave primaria o única. Crea tú el índice en la columna de la clave foránea, ya que los joins y los borrados en la tabla padre la buscan.
¿Cómo elimino una clave foránea en PostgreSQL?
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Las claves foráneas sin nombre se llaman table_column_fkey; \d orders en psql muestra el nombre exacto.
¿Una columna de clave foránea puede ser NULL en PostgreSQL?
Sí. Un NULL en la columna de la clave foránea no se comprueba, así que la fila no necesita padre. Añade NOT NULL cuando todas las filas deban tener uno.
¿Una clave foránea puede referenciar la misma tabla?
Sí. Una columna como manager_id int REFERENCES employees (id) apunta a otra fila de la misma tabla. Así se guardan árboles como organigramas e hilos de comentarios.