How to find all the foreign keys of a DB2 table TAB1?

DB2DatabaseBig Data Analytics

The foreign key of a DB2 table can be found using SYSIBM.SYSFOREIGNKEYS table and SYSIBM.SYSRELS table. The SYSFOREIGNKEYS is a DB2 system table which contains one row for every column of every foreign key. The SYSRELS table contains details about the referential constraints. In order to find out the foreign key(s) of any table, we can use the below SQL query.

SELECT B.REFTBNAME AS PARENTTABLE,COLNAME
FROM SYSIBM.SYSFOREIGNKEYS A, SYSIBM.SYSRELS B
WHERE A.RELNAME = B.RELNAME
AND B.TBNAME = 'TAB1'
AND B.REFTBCREATOR = A.CREATOR

We will join SYSFOREIGNKEYS and SYSRELS table for the columns RELNAME which stores the details regarding constraint name for the constraint for which the column is part of the foreign key.

raja
Published on 12-Sep-2020 14:40:08
Advertisements