Обсуждение: Will a DELETE violate an FK?

> Is there anyway to know if a DELETE will violate an FK
> without actually trying it?

I don't know what you mean by 'without trying it', but does the
following answer your question?

CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (id integer PRIMARY KEY,
      a_id integer NOT NULL CONSTRAINT b_fkey REFERENCES a(id));

INSERT INTO a (id) VALUES (1);
INSERT INTO b (id, a_id) VALUES (42, 1);

DELETE FROM a WHERE id=1;
ERROR:  update or delete on table "a" violates foreign key constraint
"b_fkey" on table "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".

Yours,
Laurenz Albe

On 05/29/07 09:48, Robert James wrote:
> I'd like to be able to detect if a record has associations. I don't want
> to actually delete it, just know if it could be deleted. (This is to
> build an intelligent menu on a GUI)

Are you wanting to know this in a generic way or for a specific
database?

P.S. - Please don't top-post.

> On 5/29/07, *Albe Laurenz* <all@adv.magwien.gv.at
> <mailto:all@adv.magwien.gv.at>> wrote:
>
>      > Is there anyway to know if a DELETE will violate an FK
>      > without actually trying it?
>
>     I don't know what you mean by 'without trying it', but does the
>     following answer your question?
>
>     CREATE TABLE a (id integer PRIMARY KEY);
>     CREATE TABLE b (id integer PRIMARY KEY,
>           a_id integer NOT NULL CONSTRAINT b_fkey REFERENCES a(id));
>
>     INSERT INTO a (id) VALUES (1);
>     INSERT INTO b (id, a_id) VALUES (42, 1);
>
>     DELETE FROM a WHERE id=1;
>     ERROR:  update or delete on table "a" violates foreign key constraint
>     "b_fkey" on table "b"
>     DETAIL:  Key (id)=(1) is still referenced from table "b".
>
>     Yours,
>     Laurenz Albe
>
>


--
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

am  Tue, dem 29.05.2007, um 10:48:21 -0400 mailte Robert James folgendes:
> I'd like to be able to detect if a record has associations. I don't want to
> actually delete it, just know if it could be deleted. (This is to build an
> intelligent menu on a GUI)

Set a savepoint, try to delete the record, catch an error if an error
occurs and rolled back.


>
> On 5/29/07, Albe Laurenz <all@adv.magwien.gv.at> wrote:

And please, no top-posting.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On May 29, 2007, at 9:48 , Robert James wrote:

> I'd like to be able to detect if a record has associations. I don't
> want to actually delete it, just know if it could be deleted. (This
> is to build an intelligent menu on a GUI)
>
> On 5/29/07, Albe Laurenz <all@adv.magwien.gv.at> wrote: > Is there
> anyway to know if a DELETE will violate an FK
> > without actually trying it?
>
> I don't know what you mean by 'without trying it', but does the
> following answer your question?
>
> CREATE TABLE a (id integer PRIMARY KEY);
> CREATE TABLE b (id integer PRIMARY KEY,
>       a_id integer NOT NULL CONSTRAINT b_fkey REFERENCES a(id));
>
> INSERT INTO a (id) VALUES (1);
> INSERT INTO b (id, a_id) VALUES (42, 1);

[Please don't top post. It makes referencing the previous text more
difficult.]

It sounds like you can find what you want just by using JOIN or one
of the workarounds for SEMIJOIN. Continuing with Albe's example:

INSERT INTO a (id) VALUES (2), (3), (4), (5);
INSERT INTO b(id, a_id) VALUES (43, 3), (45, 5);

Records that match (using JOIN):

SELECT *
FROM (
      SELECT id as a_id
      FROM a) AS a
NATURAL JOIN b;
a_id | id
------+----
     1 | 42
     3 | 43
     5 | 45
(3 rows)


Records that don't match (SEMIJOIN workaround using EXCEPT)

SELECT *
FROM (
      SELECT id as a_id
      FROM a) AS a
EXCEPT
SELECT a_id
FROM b;
a_id | id
------+----
     2 |
     4 |
(2 rows)

Records that don't match (SEMIJOIN workaround using LEFT JOIN)

SELECT *
FROM (
      SELECT id AS a_id
      FROM a) AS a
LEFT JOIN b USING (a_id)
WHERE id IS NULL;
a_id | id
------+----
     2 |
     4 |
(2 rows)

Both records that match and don't match using LEFT JOIN:

SELECT *
        , CASE WHEN id IS NULL
               THEN FALSE
               ELSE TRUE
          END AS has_record
FROM (
      SELECT id AS a_id
      FROM a) AS a
LEFT JOIN b USING (a_id);
a_id | id | has_record
------+----+------------
     1 | 42 | t
     2 |    | f
     3 | 43 | t
     4 |    | f
     5 | 45 | t
(5 rows)

Note you don't need the has_record column, but it might be helpful to
pass that to your application.

Hope this helps.

Michael Glaesemann
grzm seespotcode net