violates foreign key constraint in PostgreSQL, measured

ERROR: insert or update on table "child" violates foreign key constraint "child_parent_id_fkey" (SQLSTATE 23503) means you tried to store a reference to a parent row that does not exist; DETAIL names the value. Its mirror image, update or delete on table "parent" violates foreign key constraint … on table "child", means you tried to remove or re-key a parent row that child rows still point to. Either way the statement did nothing, and a transaction around it is now aborted.

We measured everything below on PostgreSQL 17.11 and repeated it on 12.22 and 18.6. The messages were identical apart from one change in 18, which we also checked on 16.15.

What do the two messages mean?

create table parent (id int primary key);
create table child (id int primary key, parent_id int references parent(id));
insert into parent values (1);
insert into child values (1, 1);

insert into child values (2, 99);
-- ERROR:  23503: insert or update on table "child" violates foreign key constraint "child_parent_id_fkey"
-- DETAIL:  Key (parent_id)=(99) is not present in table "parent".

delete from parent where id = 1;
-- ERROR:  23503: update or delete on table "parent" violates foreign key constraint "child_parent_id_fkey" on table "child"
-- DETAIL:  Key (id)=(1) is still referenced from table "child".

Updating a child to a missing parent gives the first message; updating a parent’s referenced key gives the second. A child with parent_id NULL is not checked at all. truncate parent fails with a different code, 0A000: cannot truncate a table referenced in a foreign key constraint.

What you did	What PostgreSQL said	What to do
inserted a child before its parent	`Key (parent_id)=(1) is not present in table "parent"`	parent first, or a deferred key
deleted a parent with children (`NO ACTION`, the default)	`Key (id)=(1) is still referenced from table "child"`	delete the children first, or `ON DELETE CASCADE` / `SET NULL`
the same with `ON DELETE RESTRICT`, on 18	`23001`, `violates RESTRICT setting of foreign key constraint …`, `Key (id)=(2) is referenced from table …`	as above, and catch `23001` too
deferred key, a parent never arrived	the insert message at `COMMIT`; the commit rolled back	check before committing
`MATCH FULL`, one key column `NULL`	`MATCH FULL does not allow mixing of null and nonnull key values.`	all key columns `NULL`, or none
row-level security on the table whose key is reported, or no `SELECT` on its key columns	`Key is not present in table "r_parent".` — no values	take the values from your own input
`VALIDATE CONSTRAINT` with orphans in the table	the insert message, with one orphan’s key	repair the orphans, validate again

What is the difference between NO ACTION and RESTRICT?

The documentation: “NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.” A plain delete of a referenced parent failed under both; under CASCADE the child was deleted, under SET NULL it stayed with an empty p_id. The differences we measured:

ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED still failed at the DELETE, not at commit.
One statement that deletes the parent and inserts the same key again, with d as (delete from p where id = 1 returning id) insert into p select id from d, succeeded under NO ACTION and failed under RESTRICT.
On 18.6 a RESTRICT violation has its own SQLSTATE, 23001: update or delete on table "p" violates RESTRICT setting of foreign key constraint "c_r_p_id_fkey" on table "c_r", DETAIL: Key (id)=(2) is referenced from table "c_r". On 12.22, 16.15 and 17.11 the same delete gave the ordinary 23503. ON UPDATE RESTRICT gave the same 23001 on 18.6. The 18 release notes (18.0 to 18.6) do not mention the change; we found it in the source (ri_triggers.c). Code that maps only 23503 to “still in use” misses this after an upgrade.

Why does the error appear at COMMIT?

Because the key is deferred. With DEFERRABLE INITIALLY DEFERRED, delete from pd where id = 1 succeeded, the next statement ran, and COMMIT failed with the full delete message. The whole transaction was rolled back. Re-inserting the parent, or deleting its children, before the commit made it succeed.

That also solves load order. An insert or COPY into the child table before its parent failed on the first row, and everything after it got 25P02. With the key deferred, COPY child followed by COPY parent committed. When several parents were missing, the error at commit named only the first (rows with parent 7, 8 and 9: Key (parent_id)=(7)).

set constraints all deferred affects only keys declared DEFERRABLE: on a plain foreign key it answered SET CONSTRAINTS, and the delete still failed immediately. On a DEFERRABLE INITIALLY IMMEDIATE key it did defer, and a later set constraints all immediate raised the error at that point. That lets you check before COMMIT. A commit that fails is easy to miss in code that assumes it cannot, and all earlier statements are lost with it. That is the same trap as current transaction is aborted.

Do NULLs in a composite foreign key skip the check?

Yes, with the default MATCH SIMPLE. On a key (tenant_id, parent_id), rows (999, null) and (null, 999) were accepted, although no such parent existed; only (999, 10) was checked and refused. With MATCH FULL the mixed rows (999, null) and (1, null) were refused with MATCH FULL does not allow mixing of null and nonnull key values., and (null, null) was accepted. If a key column must never be empty, use NOT NULL.

Does a foreign key stop a child pointing at another tenant’s row?

Not if it references only id. With customer_id references cust(id), an invoice of tenant 1 pointing at a customer of tenant 2 went in. With foreign key (tenant_id, customer_id) references cust (tenant_id, id) it failed with Key (tenant_id, customer_id)=(1, 200) is not present in table "cust". That composite key needs unique (tenant_id, id) on the parent, even though id is already the primary key. Without it, CREATE TABLE failed with 42830: there is no unique constraint matching given keys for referenced table.

Does row-level security hide the parent from the check?

No. The parent table had row-level security with FORCE ROW LEVEL SECURITY and a tenant policy. As tenant 1, neither the table owner nor a separate application role could see tenant 2’s parent in a SELECT. Yet both could insert a child referencing it. A reference to an id that did not exist failed. So the check ignores the policy, and the outcome tells a caller whether an id exists in another tenant. The composite key above closes that, provided the child table’s tenant_id is fixed by a policy on the child table as well: without one, a tenant-1 session could insert (tenant_id 2, parent 20) and the leak stayed open. The policies themselves are in row-level security for a multi-tenant SaaS.

What PostgreSQL does hide is the values in DETAIL, depending on the table whose key is reported:

Deleting a parent when row-level security applies to your role on the parent table: DETAIL: Key is still referenced from table "r_child". As superuser, the same delete showed Key (id)=(10).
Inserting a child with a missing parent when the child table has row-level security, or the role has INSERT but no SELECT on it: DETAIL: Key is not present in table "r_parent". After grant select (parent_id) on that one column, the values came back.
No SELECT on the parent: the insert still showed Key (parent_id)=(99), a value from the child.

A tenant-1 parent could not be deleted while the only child pointing at it belonged to tenant 2 and was invisible to the role: the error tells tenant 1 that another tenant references its row. This was the same on 12.22, 17.11 and 18.6.

How do orphans get in, and how do you find them?

One way is session_replication_role = replica. The documentation says that “since foreign keys are implemented as triggers, setting this parameter to replica also disables all foreign key checks”. As superuser we inserted children of non-existent parents and deleted a referenced parent; all succeeded. An ordinary role got 42501: permission denied to set parameter "session_replication_role". From PostgreSQL 15 on (measured on 16, 17 and 18), grant set on parameter session_replication_role lets a role do it anyway; on 12 that is a syntax error. Afterwards the key still showed convalidated = t in pg_constraint, with orphans in the table: the catalog does not tell you. Later updates of an orphan that left parent_id alone, even set parent_id = parent_id, succeeded.

select c.* from o_child c
where c.parent_id is not null
  and not exists (select 1 from o_parent p where p.id = c.parent_id);
-- 3 rows: parent_id 1, 2 and 3

Re-adding the key failed on the existing rows. With not valid it succeeded and refused new orphans at once. validate constraint then gave the insert message with one orphan’s key, Key (parent_id)=(3) is not present in table "o_parent". After the orphans were deleted it succeeded, and pg_constraint.convalidated went from f to t. The same two steps keep a migration on a large table short; see schema migrations on a shared multi-tenant table.

Why did my insert wait and then fail?

Because another transaction was deleting the parent. A deleted the parent and kept its transaction open; B inserted a child of that parent.

B waited until A committed, with pg_blocking_pids naming A, then got Key (parent_id)=(1) is not present; when A rolled back instead, B’s insert succeeded. In REPEATABLE READ the error was 40001: could not serialize access due to concurrent update. Its CONTEXT showed the internal check: SELECT 1 FROM ONLY "public"."w_parent" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x. A new attempt after A’s commit is the plain case above, 23503; how to retry a 40001 is in could not serialize access. The reverse — deleting a parent while another session’s child insert was uncommitted — also waited. It then failed with still referenced after that commit, or succeeded after a rollback. On 12.22 and 18.6 we repeated the case where A commits, with the same result.

What we did not measure

Supabase, which runs PostgreSQL with its own roles on top. Foreign keys to partitioned tables, and SET DEFAULT. The cost of a missing index on the referencing column, which the documentation calls “often a good idea” because a parent delete has to scan the child table. How ORMs translate 23503 and the new 23001.

Sources: PostgreSQL 17 documentation on constraints (NO ACTION vs RESTRICT, MATCH FULL, indexes) and client connection defaults (session_replication_role), read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11, 18.6, 16.15 and 12.22 in containers, on 22 October 2026.