Thread: how to show foreign keys of a table
Hi, in psql, the command "\d <table>" gives me the table-definitions, but the defined foreign keys don't show up. How can I get those? How can I check, what foreign keys a table has? Thanx
On Tue, 26 Dec 2000, T. Dekany wrote:
> Hi,
> in psql, the command "\d <table>" gives me the table-definitions, but
> the defined foreign keys don't show up. How can I get those? How can I
> check, what foreign keys a table has?
> Thanx
This was posted to the list a while ago, compliments of Michael Fork
<mfork@toledolink.com>:
SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname= '<< TABLENAME >>>')
AND (pp.proname LIKE '%%ins')
AND (pg_proc.proname LIKE '%%upd')
AND (pg_proc_1.proname LIKE '%%del')
AND (pg_trigger.tgrelid=pt.tgconstrrelid)
AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
On Fri, Dec 29, 2000 at 10:06:14PM -0600, some SMTP stream spewed forth: > On Tue, 26 Dec 2000, T. Dekany wrote: > > > Hi, > > in psql, the command "\d <table>" gives me the table-definitions, but > > the defined foreign keys don't show up. How can I get those? How can I > > check, what foreign keys a table has? > > Thanx > > This was posted to the list a while ago, compliments of Michael Fork Er, it smells like that only shows triggers, rather than foreign key constraints. Correct? I too need to view foreign keys. Thanks. gh > <mfork@toledolink.com>: > > SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, > pg_proc.proname, pg_proc_1.proname FROM pg_class pc, > pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, > pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt > WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid > AND pg_trigger.tgconstrrelid = pc.oid > AND pg_proc.oid = pg_trigger.tgfoid > AND pg_trigger_1.tgfoid = pg_proc_1.oid > AND pg_trigger_1.tgconstrrelid = pc.oid > AND ((pc.relname= '<< TABLENAME >>>') > AND (pp.proname LIKE '%%ins') > AND (pg_proc.proname LIKE '%%upd') > AND (pg_proc_1.proname LIKE '%%del') > AND (pg_trigger.tgrelid=pt.tgconstrrelid) > AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)); > > -- > Dominic J. Eidson > "Baruk Khazad! Khazad ai-menu!" - Gimli > ------------------------------------------------------------------------------- > http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ > >
On Sat, Dec 30, 2000 at 11:23:12PM -0600, some SMTP stream spewed forth: > On Fri, Dec 29, 2000 at 10:06:14PM -0600, some SMTP stream spewed forth: > > On Tue, 26 Dec 2000, T. Dekany wrote: > > > > > Hi, > > > in psql, the command "\d <table>" gives me the table-definitions, but > > > the defined foreign keys don't show up. How can I get those? How can I > > > check, what foreign keys a table has? > > > Thanx > > > > This was posted to the list a while ago, compliments of Michael Fork > > Er, it smells like that only shows triggers, rather than foreign key > constraints. Correct? Ah hell, foreign keys constraints are triggers, right? > > I too need to view foreign keys. I need sleep. gh > > Thanks. > > gh > > > <mfork@toledolink.com>: > > > > SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, > > pg_proc.proname, pg_proc_1.proname FROM pg_class pc, > > pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, > > pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt > > WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid > > AND pg_trigger.tgconstrrelid = pc.oid > > AND pg_proc.oid = pg_trigger.tgfoid > > AND pg_trigger_1.tgfoid = pg_proc_1.oid > > AND pg_trigger_1.tgconstrrelid = pc.oid > > AND ((pc.relname= '<< TABLENAME >>>') > > AND (pp.proname LIKE '%%ins') > > AND (pg_proc.proname LIKE '%%upd') > > AND (pg_proc_1.proname LIKE '%%del') > > AND (pg_trigger.tgrelid=pt.tgconstrrelid) > > AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)); > > > > -- > > Dominic J. Eidson > > "Baruk Khazad! Khazad ai-menu!" - Gimli > > ------------------------------------------------------------------------------- > > http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ > > > >
On Sat, 30 Dec 2000, GH wrote:
> > Er, it smells like that only shows triggers, rather than foreign key
> > constraints. Correct?
>
> Ah hell, foreign keys constraints are triggers, right?
To quote Mike's email:
Date: Mon, 4 Dec 2000 23:28:32 -0500 (EST)
From: Michael Fork <mfork@toledolink.com>
To: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] SQL to retrieve FK's, Update/Delete action,
etc. (fwd)
There ya go, I figured it out :) Given the name a table, this query will
return all foreign keys in that table, the table the primary key is in,
the name of the primary key, if the are deferrable, if the are initially
deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To
get the foreign keys and primary keys and tables, you must parse the
null-terminated pg.tgargs.
Good night :)
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/