Re: Checking for Foreign Keys constraining a record?

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