Violating foreign key constraint with deferred constraint

Question

I'm trying to set my sql scripts into a transaction to achieve atomicity with my database.

The table structure is (simplified):

CREATE TABLE foo (
    id serial  NOT NULL,
    foo varchar(50)  NOT NULL,    
    CONSTRAINT foo_pk PRIMARY KEY (id)
);

CREATE TABLE access (
    id serial  NOT NULL,
    foo_id int  NULL
    CONSTRAINT access_pk PRIMARY KEY (id)
);

ALTER TABLE access ADD CONSTRAINT access_foo
    FOREIGN KEY (foo_id)
    REFERENCES foo (id)
    ON DELETE  CASCADE 
    ON UPDATE  CASCADE 
    DEFERRABLE 
    INITIALLY DEFERRED;

In my code I first declare: client.query('BEGIN'); (I'm using npm library 'pg') then insert a row into a table 'foo', then another insert to 'access' with a foo_id from the first insert. After that there is client.query('COMMIT');

All of this is in a try catch, and in the catch is client.query('ROLLBACK'); and the rolling back seems to be working if there is an issue either of the inserts. When everything should be committed I still end up in the catch block for this:

message: "insert or update on table "access" violates foreign key constraint "access_foo""

detail: "Key (foo_id)=(20) is not present in table "foo"."

I thought that deferring constraint would be enough to do this, but I guess I'm wrong. Any help is welcome.

DEFERRABLE INITIALLY DEFERRED in PostgreSQL validates the foreign key constraint at the end of the transaction. I would guess there's something wrong with your transaction demarcation. — The Impaler
There is literally verything you need to know in the error message: Key (foo_id)=(20) is not present in table "foo" So you are inserting into access foo_id=20 but in to there is no row with id=20. Most likely your INSERT INTO fee fails. — fhossfel

The Impaler The Impaler · Accepted Answer · 2020-06-09T16:33:55

You probably have some issue with the transaction demarcation. I ran a simple test and works wells.

insert into foo (id, foo) values (1, 'Anne');

start transaction;

insert into access (id, foo_id) values (101, 1);

insert into access (id, foo_id) values (107, 7); -- 7 does not exist yet...

insert into foo (id, foo) values (7, 'Ivan'); -- 7 now exists!

commit; -- at this point all is good

See running example at DB Fiddle.

Violating foreign key constraint with deferred constraint

1 Answers