Re: Will a DELETE violate an FK?

[Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>]

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


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