From: | Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Foreign key checks/referential integrity. |
Date: | 2001-04-17 08:58:36 |
Message-ID: | Pine.LNX.4.21.0104171831230.29308-100000@linuxworld.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi guys,
I've just come up with a hypothetical which, in my opinion, points to a
flaw in the foreign key implementation in Postgres. All tests were
conducted on 7.1beta4 -- not the most up to date, but I have seen no
reference to this in the mailing list/todo (ie, in 'foreign' under
TODO.detail).
See as follows:
test=# create table a (a int, primary key(a));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
table
'a'
CREATE
test=# create table b (b int references a(a) match full, primary key(b));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'b_pkey' for
table
'b'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# insert into a values(1);
INSERT 1754732 1
test=# insert into a values(2);
INSERT 1754733 1
test=# insert into a values(3);
INSERT 1754734 1
test=# insert into b values(1);
INSERT 1754735 1
test=# insert into b values(2);
INSERT 1754736 1
test=# delete from a;
ERROR: <unnamed> referential integrity violation - key in a still
referenced from b
test=# select * from a;
a
---
1
2
3
----
Now, table a has more tuples than b. In my opinion, the integrity test
relates only to those records in a which are in b (since it is a foreign
key reference). Isn't then the query valid for those tuples which do not
result in a violation of the referential integrity test? Shouldn't those
tuples in a be deleted?
Gavin
From | Date | Subject | |
---|---|---|---|
Next Message | Henryk Szal | 2001-04-17 09:09:22 | Re: timeout on lock feature |
Previous Message | Henryk Szal | 2001-04-17 08:44:12 | Re: timeout on lock feature |