Re: Modifying FK constraints

On Wed, 10 Nov 2004, Oleg Lebedev wrote:

>
> In order to find all FK declared on a table I query tg_trigger view. The
> query lists all FKs declared on the table as well as all the ones
> referencing the table. I noticed that FKs that are declared on the table
> have pgtype equal to 21, and FKs referencing the table have pgtype 9 or
> 17.
>
> The following query lists all the FKs declared table 'objective'. Is
> this the right way to do this?
>
> select t.tgconstrname, src.relname, dest.relname, t.tgtype
> from pg_trigger t, pg_class src, pg_class dest
> where t.tgrelid=src.oid
> and t.tgconstrrelid=dest.oid
> and t.tgisconstraint = 't'
> and t.tgtype=21
> and src.relname='objective';

I think that should work as long as you aren't going around making your
own constraint triggers.

I believe you can get info from pg_constraint as well in recent versions,
maybe something like:

select pg_constraint.conname, c.relname, c2.relname from
pg_constraint,pg_class c, pg_class c2 where contype='f' and conrelid=c.oid
and confrelid=c2.oid and c.relname='objective';

You can also get some other information that's hard to get from the
triggers like the referential actions (pg_constraint.confupdtype and
confdeltype).