Блог

Главная - Вопросы по программированию - Как работает множественная вставка postgresql, когда в самой таблице есть ограничение внешнего ключа?

Как работает множественная вставка postgresql, когда в самой таблице есть ограничение внешнего ключа?

#sql #postgresql #sql-insert #multiple-insert

#sql #postgresql #sql-вставка #множественная вставка

Вопрос:

Допустим, у меня есть schools таблица

 CREATE TABLE "public"."schools" (
    "id" text NOT NULL,
    "ancestor_id" text,
    CONSTRAINT "schools_ancestor_id_fkey" FOREIGN KEY ("ancestor_id") REFERENCES "public"."schools"("id"),
    PRIMARY KEY ("id")
);

Я хочу вставить несколько значений для schools 

 INSERT INTO schools (id, ancestor_id) VALUES ('school 1', 'ancestor 1'), ('ancestor 1', NULL)

Я ожидал, что запрос insert вызовет исключение внешнего ключа, но на самом деле он успешно вставляет 2 строки.

Кто-нибудь знает, почему это так? Я просмотрел документ для вставки, но в нем не упоминаются какие-либо случаи, подобные этому.

Ответ №1:

Ограничения внешнего ключа реализуются с помощью триггеров в PostgreSQL. Это не задокументировано напрямую, но вы можете увидеть это косвенно здесь:

Можно отключить или включить один триггер, указанный по имени, или все триггеры в таблице, или только пользовательские триггеры (этот параметр исключает внутренние триггеры ограничений, такие как те, которые используются для реализации ограничений внешнего ключа или отложенных ограничений уникальности и исключения).

Вы также можете увидеть это с помощью

 SELECT * FROM pg_trigger
WHERE tgrelid = 'schools'::regclass;

Правила запуска триггеров документированы и применяются также к внешним ключам:

BEFORE Триггеры на уровне строк срабатывают непосредственно перед обработкой конкретной строки, в то время как триггеры на уровне строк AFTER срабатывают в конце инструкции (но перед любыми AFTER триггерами на уровне инструкций).

(Выделение мое)

Таким образом, внешние ключи проверяются после выполнения инструкции complete.

Комментарии:

1. спасибо @Laurenz за подробности. Теперь я знаю, что foreign key использует триггер для проверки ограничения. Но я не могу найти документ, в котором мы знаем, что триггер, реализованный для ограничения внешнего ключа, является уровнем AFTER. Вы случайно не знаете, где я могу это найти?

2. Это во второй цитате из документации. Я выделил соответствующую часть жирным шрифтом.

Ответ №2:

Внешний ключ вычисляется после завершения выполнения инструкции, а не после каждой строки.

Вот почему удаление их в одном операторе также работает:

 delete from schools 
where id in ('school 1', 'ancestor 1');