Given the recent discussions of deferrable not null constraints and my on manual referential integrity work, I came up with a case where deferrable check constraints may make a lot of sense, namely with custom referential integrity handling and table inheritance. This addresses a generalized and well-understood object-oriented problem which can occur in the database, namely object substitutability. I would expect this sort of problem to crop up more often with GIS work and the like, particularly with partitioned tables, but here's a brief theoretical overview.
Suppose I have a table:
CREATE TABLE my_rectangle (
id serial primary key,
height numeric not null,
width numeric not null
);
And I want to have another table which re-uses operations and functions associated with rectangles:
CREATE TABLE my_square (CHECK (height = width) ) INHERITS (my_rectangle);
In order to enforce uniqueness and referential integrity, I would need custom check constraints and triggers. I could do it all via triggers, but check constraints would be semantically simpler if they would work.
The case occurs when I want to alter a square such that it is no longer a square, say doubling the height while leaving the width constant. The easiest solution would be to "move" the row from my_square to my_rectangle. Doing so though poses ordering issues and I either have to defer check constraints, triggers, or both in order to ensure inheritance-tree-wide uniqueness.
Keep in mind that check constraints can call functions which can look up data in other tables. For this reason there may be the same reasons to defer them as one would see with triggers.
Best Wishes,
Chris Travers
