sql - 当表本身存在外键约束时，postgresql 多次插入如何工作？

Question

假设我有一个 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)

我原以为插入查询会引发外键异常，但实际上它成功插入了 2 行。

有人知道这是为什么吗？我查看了插入文档，但没有提到任何类似的情况。

Answer 1

外键约束通过 PostgreSQL 中的触发器实现。这没有直接记录，但您可以间接看到它here :

One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints).

你也可以用

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

触发器的触发规则are documented并适用于外键:

Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

(强调我的)

所以外键是在完整的语句完成后验证的。