Обсуждение: Checking for Foreign Keys constraining a record?
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. 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 -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
Benjamin Smith <lists@benjamindsmith.com> writes:
> 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?"
Short of your own fancy function that walks the FK tree, no. (BTW,
this could be simple actually if the FK linkage is shallow.)
Add a statement to prevent the nuisance error message to the trans.
begin;
set log_min_messages to log;
do trial delete;
rollback;
HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
Forgot to add; another option is to use a PL function with an exception handler. This may be a bit more elegant approach but not necessarily easier. FYI Jerry Sievers <jerry@jerrysievers.com> writes: > Benjamin Smith <lists@benjamindsmith.com> writes: > > > 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?" > > Short of your own fancy function that walks the FK tree, no. (BTW, > this could be simple actually if the FK linkage is shallow.) > > Add a statement to prevent the nuisance error message to the trans. > > begin; > set log_min_messages to log; > do trial delete; > rollback; > > HTH > > > -- > ------------------------------------------------------------------------------- > Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant > 305 321-1144 (mobile http://www.JerrySievers.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobile http://www.JerrySievers.com/
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 > >
Вложения
This is pretty ugly but you can query pgsql for table attributes... replace tablename and you'll get the schema for a table including primary and foreign keys. You could shrink it down and look just for the foreign key. SELECT f.attnum AS number, f.attname AS name, f.attnum, f.attnotnull AS notnull, f.atthasdef as default, pg_catalog.format_type (f.atttypid,f.atttypmod) AS type, CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey ) LEFT JOIN pg_class AS g ON p.confrelid = g.oid WHERE c.relkind = 'r'::char AND c.relname = 'tableName' AND f.attnum > 0 ORDER BY number; Hope this helps, Gavin On Apr 27, 2006, at 5:25 PM, Jerry Sievers wrote: > Benjamin Smith <lists@benjamindsmith.com> writes: > >> 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?" > > Short of your own fancy function that walks the FK tree, no. (BTW, > this could be simple actually if the FK linkage is shallow.) > > Add a statement to prevent the nuisance error message to the trans. > > begin; > set log_min_messages to log; > do trial delete; > rollback; > > HTH > > > -- > ---------------------------------------------------------------------- > --------- > Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant > 305 321-1144 (mobile http://www.JerrySievers.com/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match Gavin M. Roy 800 Pound Gorilla gmr@ehpg.net
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!