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



Tue, 17 Apr 2001 18:58:36 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Foreign key checks/referential integrity.

Hi,

...
> 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?

The "all or nothing" approach causes this. And _here_ **I think** its
correct behaviour. (IMHO user and backend transactions are not the same
thing).

// Jarmo


Tue, 17 Apr 2001 21:08:06 +0200
"Jarmo Paavilainen" <netletter@comder.com>
SV: Foreign key checks/referential integrity.