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

From Kenneth Downs
Subject Re: Checking for Foreign Keys constraining a record?
Date
Msg-id 4451629C.8000407@secdat.com
Whole thread Raw
In response to Checking for Foreign Keys constraining a record?  (Benjamin Smith <lists@benjamindsmith.com>)
List pgsql-general
Benjamin Smith wrote:

>I have a customer table (very important) and have numerous fields in other
>tables FK to the serial id of the customer table.
>
>
>
What you need is a list of the foreign key definitions, out of which you
build SQL selects that check each child table based on foreign key.  The
first row that hits returns true to speed things up.

One comprehensive solution is to write a program that scans the system
catalogs for the foreign key definitions table-by-table.  Then generate
a stored procedure called Has_Children_<table> for each table.  The
subroutine would take values for the primary key columns.  It would
check each child table and return true on the first one found or false
at the end.

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


Attachment

pgsql-general by date:

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