In a PostgreSQL 9.3 database, if I define tables a
and b
as follows:
CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j)
REFERENCES a (i) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO a(i) VALUES(1);
And then do the following:
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;
It produces the error below. Why is SET CONSTRAINTS
not having the desired effect?
ERROR: insert or update on table "b" violates foreign key constraint "fkey_ij" SQL state: 23503 Detail: Key (j)=(2) is not present in table "a".
Only DEFERRABLE
constraints can be deferred.
Let me suggest superior alternatives first:
INSERT
in orderReverse the sequence of the INSERT
statements and nothing needs to be deferred. Simplest and fastest - if at all possible.
Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each command and CTEs are considered to be part of single command:
WITH ins1 AS (
INSERT INTO b(j) VALUES(2)
)
INSERT INTO a(i) VALUES(2);
While being at it, you can reuse the values for the first INSERT
; safer / more convenient for certain cases or multi-row inserts:
WITH ins1 AS (
INSERT INTO b(j) VALUES(3)
RETURNING j
)
INSERT INTO a(i)
SELECT j FROM ins1;
ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
REFERENCES a (i) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; -- !!!
Then your original code works (a bit slower, as deferred constraints add cost).
db<>fiddle here
Related:
My original answer quoted the manual:
Referential actions other than the
NO ACTION
check cannot be deferred, even if the constraint is declared deferrable.
But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATE
or ON DELETE
to rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.