Message-ID: <3F1E9F6E.2090600@geog.leeds.ac.uk>
Date: Wed, 23 Jul 2003 15:45:02 +0100
From: Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Can a table have a reference to itself?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'd like to have a table in which one column has an integrity reference 
to another column within the same table, and for updates to the primary 
column to be cascaded.  The former aspect seems to work OK, but the 
latter does not.

For example:

 > create table foo (a int primary key,
b int constraint chk_a references foo(a) match full on update cascade);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE

Inserting values into foo works as expected (and hoped); setting b to be 
equal to a or to an existing value of a is fine:

 > insert into foo values (1,1);
INSERT 141920621 1
 > insert into foo values (2,2);
INSERT 141920622 1
 > insert into foo values (3,2);
INSERT 141920623 1

but setting b to be a value not yet present in a fails:

 > insert into foo values (4,5);
ERROR:  chk_a referential integrity violation - key referenced from foo 
not found in foo

So far so good, but what I'd like to do is to be able to change a value 
of a, and have this cascaded to b; however this gives an integrity 
violation error:

 > update foo set a = 5 where a = 2;
ERROR:  chk_a referential integrity violation - key referenced from foo 
not found in foo

Is what I'm trying to do possible?  In practice, the table I wish to 
create is a list of data sets; a is the id of each data set, and b 
indicates whether or not a data set is 'original' (in which case b 
should equal a) or derived from an existing data set (in which case b 
should equal the id of the data set from from which it was derived i.e. 
an existing value of a from elsewhere in the table).

(I'm currently using postgres 7.2.1 on Solaris 5.7)

Cheers,

Oliver

-- 
Oliver Duke-Williams
School of Geography, University of Leeds