Retrieving catalog information about foreign keys
The SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS tables contain information about referential constraints and the columns of the foreign key that defines the constraint.
About this task
The SYSIBM.SYSRELS table contains information about referential constraints, and each constraint is uniquely identified by the schema and name of the dependent table and the constraint name (RELNAME). The SYSIBM.SYSFOREIGNKEYS table contains information about the columns of the foreign key that defines the constraint.
Procedure
To obtain information about referential constraints and the columns of the foreign key that defines the constraint:
Query the SYSIBM.SYSRELS table or the SYSIBM.SYSFOREIGNKEYS
table.
To retrieve the constraint name, column names,
and parent table names for every relationship in which the project
table is a dependent, execute:
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 = 'DSN8D10'
AND B.CREATOR = 'DSN8D10'
AND A.TBNAME = 'PROJ'
AND B.TBNAME = 'PROJ'
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLSEQ;To
find information about the foreign keys of tables to which the project
table is a parent:
SELECT A.RELNAME, A.CREATOR, A.TBNAME, B.COLNAME, B.COLNO
FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
WHERE A.REFTBCREATOR = 'DSN8D10'
AND A.REFTBNAME = 'PROJ'
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLNO;