Thread: 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
---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

<p><font size="2">No, they shouldn't.  If you want to delete only those tuples that aren't referenced in b then you
mustexplicitly say so:</font><p><font size="2">delete from a where not exists (select * from b where b.b =
a.a);</font><p><fontsize="2">The query that you tried will explicitly delete all rows from a, thus violating the
constrainton b.  If even one row fails, then the transaction fails, rolling back any other deletes that may have been
successful.</font><p><fontsize="2">Cheers...</font><br /><p><font size="2">MikeA</font><br /><br /><p><font
size="2">>>-----Original Message-----</font><br /><font size="2">>> From: Gavin Sherry [<a
href="mailto:swm@linuxworld.com.au">mailto:swm@linuxworld.com.au</a>]</font><br/><font size="2">>> Sent: 17 April
200109:59</font><br /><font size="2">>> To: pgsql-hackers@postgresql.org</font><br /><font size="2">>>
Subject:[HACKERS] Foreign key checks/referential integrity.</font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> Hi guys,</font><br /><font size="2">>> </font><br /><font
size="2">>>I've just come up with a hypothetical which, in my opinion, </font><br /><font size="2">>>
pointsto a</font><br /><font size="2">>> flaw in the foreign key implementation in Postgres. All tests
were</font><br/><font size="2">>> conducted on 7.1beta4 -- not the most up to date, but I have seen no</font><br
/><fontsize="2">>> reference to this in the mailing list/todo (ie, in 'foreign' under</font><br /><font
size="2">>>TODO.detail).</font><br /><font size="2">>> </font><br /><font size="2">>> See as
follows:</font><br/><font size="2">>> </font><br /><font size="2">>> test=# create table a (a int, primary
key(a));</font><br/><font size="2">>> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index </font><br
/><fontsize="2">>> 'a_pkey' for</font><br /><font size="2">>> table</font><br /><font size="2">>>
'a'</font><br/><font size="2">>> CREATE</font><br /><font size="2">>> test=# create table b (b int
referencesa(a) match full, </font><br /><font size="2">>> primary key(b));</font><br /><font size="2">>>
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index </font><br /><font size="2">>> 'b_pkey' for</font><br
/><fontsize="2">>> table</font><br /><font size="2">>> 'b'</font><br /><font size="2">>> NOTICE: 
CREATETABLE will create implicit trigger(s) for FOREIGN KEY</font><br /><font size="2">>> check(s)</font><br
/><fontsize="2">>> CREATE</font><br /><font size="2">>> test=# insert into a values(1);</font><br /><font
size="2">>>INSERT 1754732 1</font><br /><font size="2">>> test=# insert into a values(2);</font><br /><font
size="2">>>INSERT 1754733 1</font><br /><font size="2">>> test=# insert into a values(3);</font><br /><font
size="2">>>INSERT 1754734 1</font><br /><font size="2">>> test=# insert into b values(1);</font><br /><font
size="2">>>INSERT 1754735 1</font><br /><font size="2">>> test=# insert into b values(2);</font><br /><font
size="2">>>INSERT 1754736 1</font><br /><font size="2">>> test=# delete from a;</font><br /><font
size="2">>>ERROR:  <unnamed> referential integrity violation - key in a still</font><br /><font
size="2">>>referenced from b</font><br /><font size="2">>> test=# select * from a;</font><br /><font
size="2">>> a</font><br /><font size="2">>> ---</font><br /><font size="2">>>  1</font><br /><font
size="2">>> 2</font><br /><font size="2">>>  3</font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> ----</font><br /><font size="2">>> </font><br /><font
size="2">>>Now, table a has more tuples than b. In my opinion, the </font><br /><font size="2">>> integrity
test</font><br/><font size="2">>> relates only to those records in a which are in b (since it </font><br /><font
size="2">>>is a foreign</font><br /><font size="2">>> key reference). Isn't then the query valid for those
tuples</font><br /><font size="2">>> which do not</font><br /><font size="2">>> result in a violation of
thereferential integrity test? </font><br /><font size="2">>> Shouldn't those</font><br /><font size="2">>>
tuplesin a be deleted?</font><br /><font size="2">>> </font><br /><font size="2">>> Gavin</font><br /><font
size="2">>></font><br /><font size="2">>> </font><br /><font size="2">>> </font><br /><font
size="2">>>---------------------------(end of </font><br /><font size="2">>>
broadcast)---------------------------</font><br/><font size="2">>> TIP 2: you can get off all lists at once with
theunregister command</font><br /><font size="2">>>     (send "unregister YourEmailAddressHere" to </font><br
/><fontsize="2">>> majordomo@postgresql.org)</font><br /><font size="2">>> </font><code><font size="3"><br
/><br/> _________________________________________________________________________<br /> This e-mail and any attachments
areconfidential and may also be privileged and/or copyright <br /> material of Intec Telecom Systems PLC (or its
affiliatedcompanies). If you are not an <br /> intended or authorised recipient of this e-mail or have received it in
error,please delete <br /> it immediately and notify the sender by e-mail. In such a case, reading, reproducing, <br />
printingor further dissemination of this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom
SystemsPLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses or other
defects.The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are not
necessarilythose of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email message
hasbeen swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code> 

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