Thread: Foreign Key Deferrable Misunderstanding or Bug?

Why does the attached script fail with a foreign key constraint violation?


Privileged/Confidential Information may be contained in this message.

If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you
maynot copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the
senderby reply email. Please advise immediately if you or your employer does not consent to Internet email for messages
ofthis kind. Opinions, conclusions and other information in this message that do not relate to the official business of
myfirm shall be understood as neither given nor endorsed by it. 
DROP TABLE IF EXISTS test_fk_def1 CASCADE;
CREATE TABLE test_fk_def1 (id SERIAL PRIMARY KEY, name TEXT);

DROP TABLE IF EXISTS test_fk_def2 CASCADE;
CREATE TABLE test_fk_def2 (id SERIAL PRIMARY KEY,
  fk INTEGER REFERENCES test_fk_def1(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
    DEFERRABLE INITIALLY DEFERRED);

INSERT INTO test_fk_def1 (name) VALUES ('one');
INSERT INTO test_fk_def2 (fk) SELECT id FROM test_fk_def1
    ORDER BY id DESC LIMIT 1;

BEGIN;
-- this should be unnecessary since it is initially deferred, but it doesn't
-- work with or without it
SET CONSTRAINTS test_fk_def2_fk_fkey DEFERRED;
DELETE FROM test_fk_def1; -- why does this fail?
DELETE FROM test_fk_def2;
COMMIT;

Paul Rogers <progers@sparkbase.com> writes:
> Why does the attached script fail with a foreign key constraint violation?

The ON DELETE RESTRICT is why.  Per the fine manual:

    [RESTRICT] is the same as NO ACTION except that the check is not
    deferrable.

It's a bit odd, but that's the best interpretation we can make of the
spec's wording about how this should work.

            regards, tom lane

On Thu, 6 Aug 2009, Paul Rogers wrote:

> Why does the attached script fail with a foreign key constraint violation?

Referential actions are not deferred when a constraint is marked
deferrable (as that appears to be what the spec wants), so ON DELETE
RESTRICT will still fail on the statement, while ON DELETE NO ACTION (ie,
only check at constraint check time) should wait to the end.