Re: Checking for Foreign Keys constraining a record?

On Thu, Apr 27, 2006 at 04:38:35PM -0700, 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.
>
> 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.

Let's say you have PK table foo with PK foo_id and FK tables bar and
quux, each with foo_id REFERENCES foo(foo_id)

You can do something like this:

SELECT f.foo_id,
    (
        b.foo_id IS NULL
    AND
        q.foo_id IS NULL
    ) AS "deletable"  /* boolean :) */
FROM
    foo f
LEFT JOIN
    bar b
    ON (f.foo_id = b.foo_id)
LEFT JOIN
    quux q
    ON (f.foo_id = q.foo_id)
;

Of course, this only reflects the state of the DB at the time the
query is issued, so you'll have to be prepared to catch errors from ON
DELETE RESTRICT anyhow.

HTH :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!