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
