RE: Foreign key checks/referential integrity.

No, they shouldn't. If you want to delete only those tuples that aren't
referenced in b then you must explicitly say so:

delete from a where not exists (select * from b where b.b = a.a);

The query that you tried will explicitly delete all rows from a, thus
violating the constraint on b. If even one row fails, then the transaction
fails, rolling back any other deletes that may have been successful.

Cheers...

MikeA

>> -----Original Message-----
>> From: Gavin Sherry [mailto:swm(at)linuxworld(dot)com(dot)au]
>> Sent: 17 April 2001 09:59
>> To: pgsql-hackers(at)postgresql(dot)org
>> Subject: [HACKERS] Foreign key checks/referential integrity.
>>
>>
>> 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
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to
>> majordomo(at)postgresql(dot)org)
>>

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________