pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan a table have a reference to itself?
3+ messages / 3 participants
[nested] [flat]
* Can a table have a reference to itself?
@ 2003-07-23 14:45 Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>
2003-07-28 03:47 ` Re: Can a table have a reference to itself? Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-28 05:31 ` Re: Can a table have a reference to itself? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Oliver Duke-Williams @ 2003-07-23 14:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can a table have a reference to itself?
2003-07-23 14:45 Can a table have a reference to itself? Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>
@ 2003-07-28 03:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-07-28 03:47 UTC (permalink / raw)
To: Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>; +Cc: pgsql-sql
On Wed, 23 Jul 2003, Oliver Duke-Williams wrote:
> 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.
I think you need to upgrade to 7.3 or better and try it there. I think
that's when those bugs were fixed.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can a table have a reference to itself?
2003-07-23 14:45 Can a table have a reference to itself? Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>
@ 2003-07-28 05:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-07-28 05:31 UTC (permalink / raw)
To: Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>; +Cc: pgsql-sql
Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk> writes:
> 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
Seems to work in 7.3.4 and CVS tip:
regression=# update foo set a = 5 where a = 2;
UPDATE 1
regression=# select * from foo;
a | b
---+---
1 | 1
3 | 5
5 | 5
(3 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-28 05:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 14:45 Can a table have a reference to itself? Oliver Duke-Williams <o.duke-williams@geog.leeds.ac.uk>
2003-07-28 03:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-28 05:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox