Re: Checking for Foreign Keys constraining a record? - Mailing list pgsql-general

From Jerry Sievers
Subject Re: Checking for Foreign Keys constraining a record?
Date
Msg-id m3odymlex3.fsf@prod01.jerrysievers.com
Whole thread Raw
In response to Checking for Foreign Keys constraining a record?  (Benjamin Smith <lists@benjamindsmith.com>)
List pgsql-general
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@jerrysievers.com> writes:

> Benjamin Smith <lists@benjamindsmith.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/

pgsql-general by date:

Previous
From: Jerry Sievers
Date:
Subject: Re: Checking for Foreign Keys constraining a record?
Next
From: Mike Leahy
Date:
Subject: Re: Help with unpredictable use of indexes on large tables...