Re: Will a DELETE violate an FK?

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
>