Checking for Foreign Keys constraining a record?

From: Benjamin Smith <lists(at)benjamindsmith(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: Checking for Foreign Keys constraining a record?
Date: 2006-04-27 23:38:35
Message-ID: 200604271638.35366.lists@benjamindsmith.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a customer table (very important) and have numerous fields in other
tables FK to the serial id of the customer table.

There's an option to delete a customer record, but it has to fail if any
records are linked to it (eg: invoices) in order to prevent the books from
getting scrambled.

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?"

Thanks,

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GUNDUZ 2006-04-27 23:43:13 For vim users: Syntax highlighting for PostgreSQL
Previous Message Tom Lane 2006-04-27 23:21:55 Re: Help with unpredictable use of indexes on large tables...