Re: viewing foreign key constraints

Here is an SQL query that I posted to the list some time ago (its in the
list archives...)

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

Here's the query that, given the primary key table, lists all foreign
keys, their tables, the RI type, and defereability.

SELECT pg_trigger.tgargs,
pg_trigger.tgnargs,
pg_trigger.tgdeferrable,
pg_trigger.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pg_class,
pg_class pg_class_1,
pg_class pg_class_2,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_trigger pg_trigger_2
WHERE pg_trigger.tgconstrrelid = pg_class.oid
AND pg_trigger.tgrelid = pg_class_1.oid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pg_class_1.oid
AND pg_trigger_2.tgconstrrelid = pg_class_2.oid
AND pg_trigger_2.tgfoid = pg_proc.oid
AND pg_class_2.oid = pg_trigger.tgrelid
AND ((pg_class.relname='<<PRIMARY KEY TABLE>>')
AND  (pg_proc.proname Like '%upd')
AND  (pg_proc_1.proname Like '%del')
AND (pg_trigger_1.tgrelid=pg_trigger.tgconstrrelid)
AND (pg_trigger_2.tgrelid = pg_trigger.tgconstrrelid))


On Wed, 17 Jan 2001, Matt Friedman wrote:

> How do I get a list of the foreign key constraints for a table?
>
> Matt Friedman
>