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

Search Discussions

  • Michael Ansley at Apr 17, 2001 at 10:27 am
    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
    Sent: 17 April 2001 09:59
    To: pgsql-hackers@postgresql.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@postgresql.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.
    __________________________________________________________________________
  • Jarmo Paavilainen at Apr 17, 2001 at 7:10 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 17, '01 at 8:56a
activeApr 17, '01 at 7:10p
posts3
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase