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
---123
----
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
