Thread: Re: ablilty to test record for foreign key before deleting the record? - Found word(s) list error in the Text body

Adding -general back to the email list.

> From: Nathan Clark [mailto:nathanc@abcsinc.com]
> The java application we are writing, throws a foreign key
> error, when the
> user tries to delete a record that has a foreign key. If
> there was a way to
> test the record for foreign keys before the user tries to
> delete, we could
> display a properly formatted error for user consumption.
>
> does Postgresql have a facility for this maybe?

The problem with doing what you propose is that it creates a race condition:

-- Check to see if there are any children
SELECT 1 FROM child WHERE parent_id = blah LIMIT 1;

-- Got back an empty set, so we can delete
-- But meanwhile someone else just inserted a child!

DELETE FROM parent WHERE parent_id = blah;
ERROR: Foreign key ...

Rather than doing this, you should just trap the error and handle it accordingly, either in your java or using a
function.http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING provides
examplesof how to trap errors in plpgsql; you'd just need to modify that for the error you're trapping on. 

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim C. Nasby
> Sent: Tuesday, November 15, 2005 12:04 PM
> To: Nathan Clark
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ablilty to test record for foreign key before
> deleting the record?
>
> On Tue, Nov 15, 2005 at 10:52:30AM -0600, Nathan Clark wrote:
> > Is there a way to check to see if a record has a foreign
> key, before I
> > try to delete a record ?
> > To check first to see if this record is tied to a foreign
> key before I
> > try to change it, thus avoiding
> > a foreign key error.
>
> Well, you could always try selecting on the child table...
>
> What are you actually trying to do? If you just want to avoid exposing
> the error you're probably better off just trapping for it in plpgsql.
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>