postgresqlconstraintsdeferrable-constraint

Why is my regular foreign key constraint not deferrable?

I have 2 simple tables with a foreign key constraint:

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

I want to defer the constraint for the duration of a transaction

START TRANSACTION;
SET CONSTRAINTS fkey_ij DEFERRED;

I get the following error:

[42809] ERROR: constraint "fkey_ij" is not deferrable

I'm reading the postgres SET CONSTRAINTS docs and don't understand why this constraint would not be eligible to be deferred:

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

Am I missing something from the docs?

Solution

  • A) The doc link is for version 9.1 which is well past EOL(~5.25 yrs).

    B) Yes, you missed the next paragraph:

    Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. .

    So the constraint needs to be created as DEFERRABLE or altered to it. See ALTER TABLE:

    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]