Внешний ключ связывает столбец с первичным ключом или уникальным столбцом другой таблицы. PostgreSQL отклоняет любое значение, для которого там нет подходящей строки. Записывается это как column type REFERENCES other_table (column). Добавьте ON DELETE CASCADE или ON DELETE SET NULL, чтобы указать, что происходит с дочерними строками при удалении родителя.
У столбца, на который ссылаются, должно быть ограничение PRIMARY KEY или UNIQUE. Если написать REFERENCES customers без столбца, PostgreSQL возьмёт первичный ключ customers.
Как выглядит нарушение
Заказ для несуществующего клиента отклоняется:
В ошибке указано ограничение orders_customer_id_fkey: такое имя PostgreSQL даёт безымянному внешнему ключу. Удаление клиента, у которого ещё есть заказы, падает так же, если ключ не задаёт другого поведения.
Варианты ON DELETE
| Вариант | Что происходит при удалении родительской строки |
|---|---|
NO ACTION (по умолчанию) | Удаление не проходит, пока есть дочерние строки |
RESTRICT | То же, но проверку нельзя отложить до конца транзакции |
CASCADE | Дочерние строки тоже удаляются |
SET NULL | Столбец внешнего ключа у потомка становится NULL |
SET DEFAULT | Столбец потомка получает значение по умолчанию |
ON UPDATE принимает те же варианты. Он срабатывает, когда меняется значение ключа у родителя, а при генерируемых id это бывает редко.
CASCADE подходит для строк, которые без родителя не имеют смысла, например позиций заказа:
Остаётся только chair. Каскад идёт и по следующим ключам, так что одно удаление может убрать строки в нескольких таблицах.
SET NULL сохраняет дочернюю строку и отвязывает её:
Ada остаётся, а team_id становится NULL. Начиная с PostgreSQL 15, ON DELETE SET NULL (column) может очищать только часть столбцов составного ключа.
Добавить внешний ключ в существующую таблицу
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
Эта команда проверяет все существующие строки и падает, если у какой-то нет родителя. На большой таблице добавляйте ключ с NOT VALID: тогда проверяются только новые строки. Затем выполните ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;, чтобы проверить старые строки, не блокируя запись. Чтобы изменить действие ON DELETE у существующего ключа, удалите ограничение и добавьте его заново в одной команде ALTER TABLE.
Индекс на столбец внешнего ключа
PostgreSQL индексирует сторону, на которую ссылаются, а не ссылающийся столбец. Без индекса на orders.customer_id каждое удаление из customers сканирует всю orders в поисках потомков, и выборка заказов одного клиента тоже:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Чтобы вывести внешние ключи таблицы, запросите pg_constraint:
Соединения по таким ключам разобраны на странице о JOIN, а полные правила, включая откладываемые ключи, есть в главе об ограничениях документации.
Часто задаваемые вопросы
Создаёт ли PostgreSQL индекс для внешнего ключа?
Для ссылающегося столбца нет. У столбца, на который ссылаются, индекс уже есть, потому что он обязан быть первичным ключом или уникальным. Индекс на столбец внешнего ключа создайте сами: по нему ищут соединения и удаления из родительской таблицы.
Как удалить внешний ключ в PostgreSQL?
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Безымянные внешние ключи называются table_column_fkey; точное имя показывает команда \d orders в psql.
Может ли столбец внешнего ключа быть NULL в PostgreSQL?
Да. NULL в столбце внешнего ключа не проверяется, такой строке родитель не нужен. Добавьте NOT NULL, если родитель должен быть у каждой строки.
Может ли внешний ключ ссылаться на ту же таблицу?
Да. Столбец вроде manager_id int REFERENCES employees (id) указывает на другую строку той же таблицы. Так хранят деревья: оргструктуры и ветки комментариев.