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.
thomaszhwangAsked:
Who is Participating?
 
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:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.