Catalog information about foreign keys.

[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.

[nicc]

Try SYSIBM.SYSFOREIGNKEYS

[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

[Disha Shetty]

Thanks nicc and Anuj.