How to retrieve FK reference infomation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

forestever@gmail.com - 12 Jan 2005 21:49 GMT

Hi,

Please find me with this, I don't which IBM sys table to query.

I am using DB2 7.1 in OS390. I need to retrieve the FK reference
information, i.e., which PK column a FK is refer to? I tried SYSRELS
table, but it only gives me the reference/parent table information.
Thanks lot!

Reply to this Message

Shyam Peri - 13 Jan 2005 11:07 GMT

Does the syscat.references table help u in what ur looking for?

Reply to this Message

Yury Jhol - 13 Jan 2005 14:34 GMT

select FK.CREATOR, FK.TBNAME, FK.COLNAME, FK.COLSEQ, PK.TBCREATOR,
PK.TBNAME, PK.NAME, R.RELNAME
from SYSIBM.SYSRELS R,
SYSIBM.SYSFOREIGNKEYS FK,
SYSIBM.SYSCOLUMNS PK
where R.CREATOR= ?
and R.TBNAME= ?
and R.RELNAME = FK.RELNAME
and R.CREATOR=FK.CREATOR
and R.TBNAME=FK.TBNAME
and R.REFTBCREATOR=PK.TBCREATOR
and R.REFTBNAME=PK.TBNAME
and FK.COLSEQ=PK.KEYSEQ
ORDER BY R.RELNAME, FK.COLSEQ asc

Yury Jhol
Chief Systems Architect
www.ispirer.com

> Hi,
>
[quoted text clipped - 4 lines]
> table, but it only gives me the reference/parent table information.
> Thanks lot!

Reply to this Message

forestever@gmail.com - 13 Jan 2005 16:50 GMT

This works, Thanks a lot!

> select FK.CREATOR, FK.TBNAME, FK.COLNAME, FK.COLSEQ, PK.TBCREATOR,
> PK.TBNAME, PK.NAME, R.RELNAME
[quoted text clipped - 23 lines]
> > table, but it only gives me the reference/parent table information.
> > Thanks lot!

Reply to this Message