RE: Foreign key checks/referential integrity.
От | Michael Ansley |
---|---|
Тема | RE: Foreign key checks/referential integrity. |
Дата | |
Msg-id | 7F124BC48D56D411812500D0B747251480F52A@fileserver002.intecsystems.co.uk обсуждение исходный текст |
Ответ на | Foreign key checks/referential integrity. (Gavin Sherry <swm@linuxworld.com.au>) |
Список | pgsql-hackers |
<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>
В списке pgsql-hackers по дате отправления:
Следующее
От: Alessio BragadiniДата:
Сообщение: Re: [PATCHES] Patch for PostgreSQL 7.0.3 to compile on Tru64 UNIX v5.0A