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
