Philip Warner <pjw@rhyme.com.au> writes:
> At 00:22 29/09/00 -0400, Tom Lane wrote:
>> Quite aside from implementation shortcomings, there are some big
>> definitional issues with subselects in constraints --- just exactly
>> what are they constraining? See past discussions in the archives.
> I presume I am missing something...
The canonical example is a check like
CHECK (mycol > (SELECT max(othertab.othercol) FROM othertab))
declared as a constraint on mytab. Here, an insertion into
othertab could mean that the constraint on mytab no longer holds
(because you inserted a value larger than some existing mytab row).
Should this mean that the constraint on mytab can prevent insertions
into othertab? Quite aside from implementation problems ---
a simplistic approach would mean re-evaluation of every constraint
in the whole DB against every row in the whole DB for every update ---
there are also interesting security issues. At first sight one would
think that the owner of mytab only needs read access on othertab to
define the above constraint, but in fact this "read access" is
sufficient to deny updates on othertab. Not good.
You can devise related paradoxes within a single table if constraints
involving aggregates are permitted. The basic problem is that it's
not clear which data values are the constrainers and which are the
constrainees.
regards, tom lane