Re: how to implement unusual constraint

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