Thread: how to implement unusual constraint
Say I have a table, say my_table, that is self-referencing. It looks like this : id integer pk, orig_id integer references my_table(id), . . . Now this set of rows would be legal id/orig_id 1 /1 2/1 3/1 4/4 5/4 but this not: id/orig_id 1 /1 2/1 3/1 4/1 5/4 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? thanks, Daniel
On sun, 2007-06-24 at 09:54 +0000, danmcb wrote: > Say I have a table, say my_table, that is self-referencing. It looks > like this : > id integer pk, > orig_id integer references my_table(id), > Now this set of rows would be legal > > id/orig_id > 1 /1 > 2/1 > 3/1 > 4/4 > 5/4 > > but this not: > > id/orig_id > 1 /1 > 2/1 > 3/1 > 4/1 > 5/4 > > 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. this might get you on the right track: test=# create table foo (i int, o int); CREATE TABLE test=# create function foo_check(int) returns int language SQL AS 'select o from foo where i=$1'; CREATE FUNCTION test=# alter table foo ADD CHECK (foo_check(o)=o); ALTER TABLE test=# insert into foo values (1,1); INSERT 0 1 test=# insert into foo values (2,1); INSERT 0 1 test=# insert into foo values (3,1); INSERT 0 1 test=# insert into foo values (4,3); ERROR: new row for relation "foo" violates check constraint "foo_o_check" test=# insert into foo values (4,4); INSERT 0 1 test=# insert into foo values (5,4); INSERT 0 1 of course this example was very incomplete. gnari
Ragnar <gnari@hive.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
"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". " I've thought about that. The obvious way to do it would be to split into two tables, one for the originals, one for the translations (the objects are actually phrases in many languages). But I'd rather avoid that because in all other ways, the objects have the same properties, reference the same objects, and so on. Splitting into two types is not really desirable, from the point of view of what they are modelling.
On Sun, Jun 24, 2007 at 06:45:00PM -0000, danmcb wrote:
> into two tables, one for the originals, one for the translations (the
> objects are actually phrases in many languages). But I'd rather avoid
> that because in all other ways, the objects have the same properties,
> reference the same objects, and so on. Splitting into two types is not
> really desirable, from the point of view of what they are modelling.
This sounds to me like you have a one:many possibility, and you
should split your columns up instead. (In general, I find, when you
have a self-join, it's probably an indicator that some of the data is
repeated, and that tells you you're not normalised correctly.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner