Hi everyone;
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