What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?

I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon COMMIT anyway.

Are there others?

If it matters, we are thinking about making all foreign keys in a certain table family DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.

TL;DR: What do we have to be aware of when making foreign keys DEFERRABLE INITIALLY DEFERRED?

  • 1
    This is a good question but there is no an answer though
    – deFreitas
    Aug 6 '18 at 4:31
  • This article may be of help to some. I know it's about Postgres, but still, there are some interesting points in it.
    – cavpollo
    May 17 at 11:25

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

  • commit might fail. This is usally not expexted by various tools and also by developers
  • commit might last very long. This might be problem with distributed(XA) transactions.
  • also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction
  • I'm sorry, I can't relate to this. It's too vague to answer the question, and it doesn't say anything about the foreign key part of it.
    – blubb
    May 26 '15 at 16:49
  • 1
    Does that mean that developers are tools? ;) I would give +1 if I could for that - but I disagree with the "Deferrable constraints are usually wrong." May 26 '15 at 17:04
  • Developers are creating tools. I saw many faulty SW where developers forgot to wrap commit into begin ... exception when ... end; block or into try-catch. Simply because nobody expected that commit can throw an exception. When you do ETL then you usualy do some DDL (disable constraints, disable indexes, drop unique indexes, ...) and then you load data. Whenever I saw deffered constraints in some schema it was implemented because company was using some crazy in-house ORM tool, which was unable serialize SQL properly.
    – ibre5041
    May 26 '15 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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