Catalog information about foreign keys.

RDBMS from IBM.
Previous topicNext topic

Topic Author
Disha Shetty
Registered Member
Posts: 18
Joined: Sat Aug 17, 2013 4:54 pm
Zodiac: Taurus

Catalog information about foreign keys.

Post by Disha Shetty » Tue Jul 12, 2016 12:38 pm

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
Global Moderator
Global Moderator
Posts: 597
Joined: Wed Apr 23, 2014 8:45 pm

Re: Catalog information about foreign keys.

Post by nicc » Tue Jul 12, 2016 5:06 pm

Try SYSIBM.SYSFOREIGNKEYS


Regards
Nic

User avatar

Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: Catalog information about foreign keys.

Post by Anuj Dhawan » Tue Jul 12, 2016 8:44 pm

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


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.


Topic Author
Disha Shetty
Registered Member
Posts: 18
Joined: Sat Aug 17, 2013 4:54 pm
Zodiac: Taurus

Re: Catalog information about foreign keys.

Post by Disha Shetty » Sat Jul 16, 2016 1:33 pm

Thanks nicc and Anuj.



Previous topicNext topic

Return to “IBM DB2.”