How to recognise primary keys for a table?
Does anyone have a select from one of the system catalog tables which enables me, for a given table, identify which columns are primary keys? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
strangiato@my-deja.com wrote:
>
> Does anyone have a select from one of the system catalog tables which
> enables me, for a given table, identify which columns are primary keys?
Here is the relevant query extracted from myschema.ec:
SELECT tabname, constrid, constrname, constrtype,
si.idxname, si.tabid, si.part1, si.part2, si.part3,
si.part4, si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12, si.part13,
si.part14, si.part15, si.part16
FROM 'informix'.systables st,
'informix'.sysconstraints sc,
'informix'.sysindexes si
WHERE st.tabid = sc.tabid
AND sc.tabid = si.tabid
AND sc.idxname = si.idxname
AND sc.constrtype = 'P'
AND st.tabname = "given_table";
Art S. Kagel
"Art S. Kagel" schrieb:
> strangiato@my-deja.com wrote:
> >
> > Does anyone have a select from one of the system catalog tables which
> > enables me, for a given table, identify which columns are primary keys?
>
> Here is the relevant query extracted from myschema.ec:
>
> SELECT tabname, constrid, constrname, constrtype,
> si.idxname, si.tabid, si.part1, si.part2, si.part3,
> si.part4, si.part5, si.part6, si.part7, si.part8,
> si.part9, si.part10, si.part11, si.part12, si.part13,
> si.part14, si.part15, si.part16
> FROM 'informix'.systables st,
> 'informix'.sysconstraints sc,
> 'informix'.sysindexes si
> WHERE st.tabid = sc.tabid
> AND sc.tabid = si.tabid
> AND sc.idxname = si.idxname
> AND sc.constrtype = 'P'
> AND st.tabname = "given_table";>
> Art S. Kagel
Interesting that the sysindexes table is a NF2 table (non first normal form).
It is not very good to have part1, part2,...part16
I think that is for performance reasons, isn't it.
But indeed it would be nice to have a normalized form in addition.
Achim
Achim Reiners wrote:
>
> "Art S. Kagel" schrieb:
>
> > strangiato@my-deja.com wrote:
> > >
> > > Does anyone have a select from one of the system catalog tables which
> > > enables me, for a given table, identify which columns are primary keys?
> >
> > Here is the relevant query extracted from myschema.ec:
> >
> > SELECT tabname, constrid, constrname, constrtype,
> > si.idxname, si.tabid, si.part1, si.part2, si.part3,
> > si.part4, si.part5, si.part6, si.part7, si.part8,
> > si.part9, si.part10, si.part11, si.part12, si.part13,
> > si.part14, si.part15, si.part16
> > FROM 'informix'.systables st,
> > 'informix'.sysconstraints sc,
> > 'informix'.sysindexes si
> > WHERE st.tabid = sc.tabid
> > AND sc.tabid = si.tabid
> > AND sc.idxname = si.idxname
> > AND sc.constrtype = 'P'
> > AND st.tabname = "given_table";> >
> > Art S. Kagel
>
> Interesting that the sysindexes table is a NF2 table (non first
> normal form).
Actually, it is in 1NF; it certainly isn't in 3NF or better.
To be in NFNF, the index parts would have to be a list in a single
attribute - hmm, roughly the way an IUS type SET OF INTEGER (probably
spelled differently) could be used.
> It is not very good to have part1, part2,...part16
Absolutely.
> I think that is for performance reasons, isn't it.
Mainly. It also wasn't as bad in SE where the limit was (and still
is) 8 parts.
> But indeed it would be nice to have a normalized form in addition.
Too right! It is really painful writing queries which identify the
names of the columns. Expecially when you remember that a negative
part number indicates a descending sort on the column identified by
the absolute value of the part number.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
In article <7mkumh$iqt$1@nnrp1.deja.com>, strangiato@my-deja.com wrote: > Does anyone have a select from one of the system catalog tables which > enables me, for a given table, identify which columns are primary keys? > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. > Yikes! , please forgive me, I meant unique indexes! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.