Re: Checking for Foreign Keys constraining a record?

Forgot to add; another option is to use a PL function with an
exception handler. This may be a bit more elegant approach but not
necessarily easier.

FYI

Jerry Sievers <jerry(at)jerrysievers(dot)com> writes:

> Benjamin Smith <lists(at)benjamindsmith(dot)com> writes:
>
> > I want to be able to determine in advance whether or not a record is
> > "deleteable" before displaying the button to delete the record. If it's not
> > deleteable, it should say so before the user hits the button.
> >
> > But, the only way that I've been able to find out if the customer record is
> > deletable is to begin a transaction, try to delete it, check to see if it
> > worked, and then rollback the session.
> >
> > This causes my error logger to log errors everytime somebody looks at a
> > customer record, and (I'm sure) is not very efficient.
> >
> > Is there a way to ask the database: "Are there any FK constraints that would
> > prevent this record from being deleted?"
>
> Short of your own fancy function that walks the FK tree, no. (BTW,
> this could be simple actually if the FK linkage is shallow.)
>
> Add a statement to prevent the nuisance error message to the trans.
>
> begin;
> set log_min_messages to log;
> do trial delete;
> rollback;
>
> HTH
>
>
> --
> -------------------------------------------------------------------------------
> Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
> 305 321-1144 (mobile http://www.JerrySievers.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/