Menu

Внешний ключ в PostgreSQL и ON DELETE CASCADE

Внешний ключ разрешает столбцу принимать только значения, которые есть в другой таблице. Разбираем REFERENCES, ON DELETE CASCADE, SET NULL и RESTRICT, добавление ключа в существующую таблицу и индекс для него.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Внешний ключ связывает столбец с первичным ключом или уникальным столбцом другой таблицы. 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) указывает на другую строку той же таблицы. Так хранят деревья: оргструктуры и ветки комментариев.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать