// справочник ошибок PostgreSQL

PostgreSQL 23503: нарушение ограничения внешнего ключа

Строка ссылается на несуществующий родительский ключ, или вы удалили либо изменили родительскую строку, на которую всё ещё ссылаются дочерние строки.

SQLSTATE
23503
Имя
foreign_key_violation
Категория
Нарушение ограничения целостности
Сообщение об ошибке
insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

Что это значит

Внешний ключ требует, чтобы каждое значение в дочернем столбце существовало в столбце, на который ссылаются (родительском). PostgreSQL вызывает 23503 в двух случаях: когда вы вставляете или обновляете дочернюю строку, ключ которой не имеет родителя (is not present in table), и когда вы удаляете или обновляете родительскую строку, на которую все еще ссылаются дочерние строки (is still referenced from table).

Этот DETAIL строка показывает значение ключа и другую таблицу, чтобы вы знали, какие строки нужно просмотреть.

Распространённые причины

  • Вставка дочерних строк перед родительскими, например загрузка orders до customers.
  • Опечатка или устаревший идентификатор в дочерней строке.
  • Удаление родительской строки, у которой еще есть дочерние строки, без ON DELETE CASCADE или SET NULL на внешнем ключе.
  • Обновление первичного ключа родительской строки, когда дочерние строки ссылаются на старое значение.
  • Две транзакции: одна вставляет дочернюю строку для родительской, которую другая только что удалила.

Как исправить

  1. Найдите отсутствующую родительскую строку или оставшиеся дочерние строки.

    SELECT * FROM customers WHERE id = 42;
    SELECT * FROM orders WHERE customer_id = 42;
  2. Сначала вставляйте родительские строки или загружайте обе таблицы в одной транзакции с отложенным ограничением (оно должно быть объявлено DEFERRABLE).

    ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE;
    BEGIN;
    SET CONSTRAINTS orders_customer_id_fkey DEFERRED;
    INSERT INTO orders (id, customer_id) VALUES (1, 42);
    INSERT INTO customers (id, name) VALUES (42, 'Ada');
    COMMIT;  -- checked here
  3. При удалении родительской записи сначала удалите или переназначьте её дочерние строки, либо измените правило удаления внешнего ключа, если вам нужно именно такое поведение.

    ALTER TABLE orders
        DROP CONSTRAINT orders_customer_id_fkey,
        ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id)
            REFERENCES customers (id) ON DELETE CASCADE;
  4. Найдите строки-сироты перед добавлением внешнего ключа к существующим данным.

    SELECT o.*
    FROM orders o
    LEFT JOIN customers c ON c.id = o.customer_id
    WHERE o.customer_id IS NOT NULL AND c.id IS NULL;

Безопасное воспроизведение

CREATE TEMP TABLE p (id int PRIMARY KEY);
CREATE TEMP TABLE c (p_id int REFERENCES p (id));
INSERT INTO c VALUES (1);  -- ERROR: ... violates foreign key constraint "c_p_id_fkey"

Связанные ошибки

Больше ошибок нарушения ограничений целостности

Код и текст сообщения взяты из Документация PostgreSQL, приложение A: коды ошибок PostgreSQL. Смотрите весь Список ошибок PostgreSQL или поиск по всем ошибкам баз данных.

// меньше ошибок, быстрее исправления

Работа с PostgreSQL в SQLly

Нативный SQL-клиент для macOS, Windows и Linux с IntelliSense, который знает вашу схему, предохранителями для рискованных запросов и понятными сообщениями об ошибках — для PostgreSQL и других баз данных, которые вы используете.