From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Benjamin Smith <lists(at)benjamindsmith(dot)com> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Checking for Foreign Keys constraining a record? |
Date: | 2006-04-28 17:01:11 |
Message-ID: | 20060428170111.GA26851@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2006-04-28 17:02:59 | Re: Installing PostgreSQL on Win 2003 R2 64-bit |
Previous Message | Andrus | 2006-04-28 16:35:20 | Re: How to define + operator for strings |