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.
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-levelAFTER
triggers fire at the end of the statement (but before any statement-levelAFTER
triggers).
(Emphasis mine)
So foreign keys are validated after the complete statement is done.