Re: how to implement unusual constraint

Ragnar <gnari(at)hive(dot)is> writes:
> On sun, 2007-06-24 at 09:54 +0000, danmcb wrote:
>> Say I have a table, say my_table, that is self-referencing. ...
>> in other words: the row pointed to by orig_id cannot reference any row
>> other than itself.
>> How might I implement this as a constraint?

> you can get around the limitation that subqueries are not allowed in
> CHECK constraints by using a function.

In general that's a last-ditch measure that's best avoided. It's got
two serious problems:

1. You've got to write explicit code for both ends of the constraint;
for example, prevent a row from being changed to have orig_id != id
if there are any rows linking to it. (And a check constraint cannot
act at row deletion at all, so you'll still need the foreign key
constraint to prevent deletion of a referenced row.)

2. There is no way to defend against contradictory concurrent updates,
since neither check constraint can "see" uncommitted changes of other
transactions. (Hmm ... actually you can probably work around that with
suitable use of SELECT FOR UPDATE or SELECT FOR SHARE, not plain SELECT,
in the checking function. But it's a whole 'nother layer of complexity
for you to deal with.)

The good thing about foreign key constraints is that those problems
are already solved for you.

So frequently the best advice for someone who's thinking of doing
something like this is "redesign your schema so you don't need to".

regards, tom lane