Adding -general back to the email list. > From: Nathan Clark [mailto:nathanc@xxxxxxxxxxx] > 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 examples of 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@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jim C. Nasby > Sent: Tuesday, November 15, 2005 12:04 PM > To: Nathan Clark > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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@xxxxxxxxxxxxx > 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 > > ---------------------------(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
