Postpone and Prosper: Make your life easier with deferring SQL constraints

For the project I am currently working on I have recently faced a small challenge. The task was to replace text in one of the IBM Informix database tables. However, for some legacy reason the text was not stored as a convenient VARCHAR or something but as a binary BLOB. The common SQL UPDATE clause doesn’t work quite well with this and the way of doing this is to DELETE the whole record and then LOAD FROM a .unl file with the updated definition.

The problem occurs when the row being deleted is also a foreign key and is being referenced from some other table. The database then won’t allow such operation.

The dumb workaround is to delete the record that references your record first, delete what you need, re-load your updated row and then also the referencing one.

It would get the job done but I saw at least two problems with this approach:

1. It is verbose and ugly
2. What if you have more foreign keys chained?

So, I was looking for something better and I found it. The solution is called

SET CONSTRAINTS <x> DEFERRED

This simple SQL command works inside a TRANSACTION and allows you to postpone constraint evaluation to the end of the transaction. Hence it allows exactly what I needed to do. Like this, I was able to delete the row and load it again with no interruption, because both operations were part of the same transaction. By the time the data should be committed into the database, the referenced FK existed again, so the database was happy. And so was I, because my SQL now looks this cool:

BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM mytable WHERE primary_key = 123;
LOAD FROM '/path/to/myfile.unl' INSERT INTO mytable;
COMMIT WORK;

Note that I used CONSTRAINTS ALL which effectively suppresses all of the constraints, but in general it is better and safer to only defer the constraint(s) you namely need to overcome. Otherwise, you put your data at risk of breaking some other rules you may not even foresee! I only did that because our project has a lot of distinct databases, and I wasn’t sure the constraint name is the same all across the system. Plus, this operation is simple and straightforward. But if you make a habit of placing ALL everywhere, it may bite you back one day.

Considerations

My use-case worked with Informix. It is possible other database systems work slightly different.

I was checking with the current ISO/IEC 9075-1 2023 SQL standard, but deferring constraints is only vaguely mentioned here. The two requirements are:

1. Every constraint is either deferrable or not deferrable.
2. A constraint mode is either deferred or immediate, and can be set by an SQL-statement, provided the constraint is deferrable.

This implies one important fact – not everything can be deferred.

This article gives a pretty good explanation for how it works in PostgreSQL. If some constraint was created as NOT DEFERRABLE, my solution just wouldn’t work.

The second crucial thing to have in mind is that database integrity constraints exist for a reason (well, usually). If you have a valid cause to break the rules, you are now aimed to do so. If not, better stay away from tampering with your database.

Conclusion

Deferring database constraints is a non-common advanced technique that is worth knowing. It took me like 20 years of hanging around databases before I learnt about it, so I decided to write this article to share my new knowledge. May it serve you well. But also keep in mind it has to be used with caution, and it probably wasn’t meant to be your universal rule-bending tool.

Since this is a new topic for me, I would also like to hear your opinions and experiences in the comments. Maybe there’s more to be said? 👀