Oninit cdi / Archive
Talk to an expert →

Re: How to find primary key?

Posted in 1999

Topics: General Discussion

Sujit.Pal@bankofamerica.com — — source: Informix-list mailing list archive (1991-1998)

Jing How about this SQL? select TRIM(d.colname) || " is a pk column for " || TRIM(b.tabname) from sysconstraints a, systables b, sysindexes c, syscolumns d where constrtype = "P" and a.tabid = b.tabid and a.tabid = c.tabid and a.tabid = d.tabid and a.idxname = c.idxname and d.colno = c.part1 and b.tabname = "mytable" and d.colname = "mycolumn" UNION ... select TRIM(d.colname) || " is a pk column for " || TRIM(b.tabname) from sysconstraints a, systables b, sysindexes c, syscolumns d where constrtype = "P" and a.tabid = b.tabid and a.tabid = c.tabid and a.tabid = d.tabid and a.idxname = c.idxname and d.colno = c.part16 and b.tabname = "mytable" and d.colname = "mycolumn" You will find more information about the system catalog tables in the SQL Reference. HTH Sujit Jing Liu <jingliu@ics.uci.edu> on 08/11/99 01:05:41 PM Please respond to Jing Liu <jingliu@ics.uci.edu> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: How to find primary key? hi, I want to find out whether a column in a table is a primary key by using system catalog in Imformix. However, I can find in sysconstraints that there are really a column is primary key, but I can not decide which column it is. syscoldepend doesn't has corresponding item for such constrid with tabid and colno. Then, given a table name and a column name, how can I decide whether it is a primary key or not? Thanks in advance.

↪ replying to Sujit.Pal@bankofamerica.com
Jing Liu — — source: Informix-list mailing list archive (1991-1998)

Thanks for the replies to my question. I've worked it out. The reference book: Informix Guide to SQL:Reference Version 9.1 doesn't list SYSINDEXES in system catalog. :( At last, I found it in a reference book of version 4.1. Jing

↪ replying to Jing Liu
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

Jing Liu wrote: > > Thanks for the replies to my question. I've worked it out. > > The reference book: Informix Guide to SQL:Reference Version 9.1 > doesn't list SYSINDEXES in system catalog. :( > At last, I found it in a reference book of version 4.1. Check /etc/system. Read the release notes, fix /etc/system, reboot. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>