Page 1 of 1
Catalog information about foreign keys.
Posted: Tue Jul 12, 2016 12:38 pm
by Disha Shetty
Hi,
Is there any way which helps to find the catalog information for the foregin keys in DB2? Or which system table should I look at to get this information Please help me on this.
Re: Catalog information about foreign keys.
Posted: Tue Jul 12, 2016 5:06 pm
by nicc
Try SYSIBM.SYSFOREIGNKEYS
Re: Catalog information about foreign keys.
Posted: Tue Jul 12, 2016 8:44 pm
by Anuj Dhawan
SYSIBM.SYSRELS contains information about referential constraints, and each constraint is uniquely identified by the creator and name of the dependent table and the constraint name (RELNAME). Extending on nicc' reply, nicc SYSIBM.SYSFOREIGNKEYS contains information about the columns of the foreign key that defines the constraint. To retrieve the constraint name, column names, and parent table names for every relationship in which the project table is a dependent, execute:
Code: Select all
SELECT A.CREATOR, A.TBNAME, A.RELNAME, B.COLNAME, B.COLSEQ,
A.REFTBCREATOR, A.REFTBNAME
FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
WHERE A.CREATOR = 'DSN8710'
AND B.CREATOR = 'DSN8710'
AND A.TBNAME = 'PROJ'
AND B.TBNAME = 'PROJ'
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLSEQ;
You can use the same tables to find information about the foreign keys of tables to which the project table is a parent, as follows:
Code: Select all
SELECT A.RELNAME, A.CREATOR, A.TBNAME, B.COLNAME, B.COLNO
FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
WHERE A.REFTBCREATOR = 'DSN8710'
AND A.REFTBNAME = 'PROJ'
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLNO;
Refer:
http://publibz.boulder.ibm.com/cgi-bin/ ... 0718164132
Re: Catalog information about foreign keys.
Posted: Sat Jul 16, 2016 1:33 pm
by Disha Shetty
Thanks nicc and Anuj.