Re: Will a DELETE violate an FK?

["Robert James" <srobertjames(at)gmail(dot)com>]

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

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