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
