• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

How to find out Primary Key information in the system tables?

In Informix, is there a way to find out the Primary Key information in the system tables?  Thanks.
0
thomaszhwang
Asked:
thomaszhwang
  • 2
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Hi. You want to check the SYSCONSTRAINTS. You specifically want to look for constrtype = 'P' for Primary key.

Hope that helps!

Kevin
0
 
OP_ZaharinCommented:
- to add further, you can use the following query, it will return the tablename for that Primarykey as the SYSCONSTRAINTS only contain the tableid :

SELECT a.tabname, constrname, d.tabname
  FROM systables a, sysconstraints b, sysreferences c,
       systables d
 WHERE b.constrtype = 'R'
   AND a.tabid = b.tabid
   AND b.constrid = c.constrid
   AND c.ptabid = d.tabid

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst47.htm
0
 
OP_ZaharinCommented:
- correction change R code to P for primary key:

SELECT a.tabname, constrname, d.tabname
  FROM systables a, sysconstraints b, sysreferences c,
       systables d
 WHERE b.constrtype = 'P'
   AND a.tabid = b.tabid
   AND b.constrid = c.constrid
   AND c.ptabid = d.tabid
0
 
Kevin CrossChief Technology OfficerCommented:
OP_Zaharin: thanks! It appears that because of the frames on IBM's site, my version of the link went to SYSCOLUMNS and not SYSCONSTRAINTS, so you provide both a wonderful code sample and the correct link. :)
0
 
thomaszhwangAuthor Commented:
Thank you guys.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now