Re: Will a DELETE violate an FK?

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(at)adv(dot)magwien(dot)gv(dot)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