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.

score 4 · Accepted Answer · answered Nov 19 '20 at 13:05

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.

thanks @Laurenz for the details. I know now that foreign key uses a trigger to validate the constraint. But I can't find the document where we know the trigger implemented for foreign key constraint is an AFTER level. Do you happen to know where I can look for it? — AngryCat, Nov 19 '20 at 13:26
That is in the second quotation from the documentation. I made the relevant part bold. — Laurenz Albe, Nov 19 '20 at 13:27

score 3 · Answer 2 · answered Nov 19 '20 at 13:03

3

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

answered Nov 19 '20 at 13:03

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

2 Answers2