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
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:ken@xxxxxxxxxx
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
