1

I have a case where I have deletions occurring in cascade in an integration test. The tests are performed on a database created from a test data dump with the additional application of the last developed SQL migrations (using Flyway as the versioning framework).

The problem is that initially the test passes and when I just update the test dump to already contain the upcoming migrations, the test fails. The error thrown is the following:

Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException: An exception occurred while executing 'DELETE FROM tableA WHERE idA=:idA' with params [XXX]:

SQLSTATE[23503]: Foreign key violation: 7 ERROR:  update or delete on table "tableC" violates foreign key constraint "tableD_idB_fkey1" on table "tableD"
DETAIL:  Key (idB, idC)=(XXX, XXX) is still referenced from table "tableD".

The situation could be avoided by a better schema design but for the sake of time, I would like to avoid that when deleting from the main table(A) and then expecting deletions in cascade to occur, we have a "conflict" between FKs. The tableD has two FKs pointing to tableB and tableC but all the rows should ultimately be properly deleted. The thing is that one of the keys is set to ON DELETE CASCADE while the other is set to NO ACTION, the one which is violated.

https://www.postgresql.org/docs/current/sql-createtable.html

I tried setting this problematic FK constraint DEFERRABLE but it is yet violated by the DELETE statement. How to get the expected graceful execution?

1 Answer 1

1

Solved. I then paid attention to the following section of the documentation:

INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

Stating the INITIALLY DEFERRED mode, I got the constraint successfully deferred. I suppose that when they say "checked after each statement", the "statement" acceptation does not take into account the cascading deletions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.