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

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