Re: Checking for Foreign Keys constraining a record?

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