How postgresql multiple insert works when there is foreign key constraint in the table itself?

Question

Let's say I have a schools table

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")
);

I want to insert multiple values for schools

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

I was expected that insert query will raise foreign key exception, but it actually inserts 2 rows successfully.

Anybody knows why is that? I looked at the document for insert but it doesn't mention any cases similar to this.

Answer 1

Foreign key constraints are implemented with triggers in PostgreSQL. This is not directly documented, but you can see it indirectly 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).

You can also see it with

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

The firing rules for triggers are documented and apply to foreign keys as well:

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).

(Emphasis mine)

So foreign keys are validated after the complete statement is done.

Answer 2

The foreign key is evaluated after the statement has finished, not after every row.

That's why deleting them in a single statement also works:

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