首先寻找PK的索引名称(pk_idx列)
select c.constrname, c.constrtype as tp , c.idxname as pk_idx , t2.tabname, c2.idxname
from sysconstraints c, systables t, outer (sysreferences r, systables t2, sysconstraints c2)
where t.tabname = "asamembr"
and t.tabid = c.tabid
and r.constrid = c.constrid
and t2.tabid = r.ptabid
and c2.constrid = r.constrid
其中的构造型:
constrtype CHAR(1)标识约束类型的代码: C =检查约束 N =不为NULL P =主键 R =引用 T =表 U =唯一
然后,检查索引列(查找与PK约束相同的索引名称):
select unique
t.tabname
, i.idxname
, i.idxtype
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part1 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part2 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part3 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part4 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part5 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part6 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part7 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part8 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part9 )
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part10)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part11)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part12)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part13)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part14)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part15)
, (select c.colname from syscolumns c where c.tabid = i.tabid and c.colno = i.part16)
from sysindexes i , systables t
where i.tabid = t.tabid
and t.tabname = "asamembr";
其中idxtype:
idxtype CHAR(1)索引类型: U =唯一 D =允许重复 G =非 位图通用g =通用位图 u =唯一,位图 d =非唯一,位图
在Informix在线手册中搜索“ sysconstraints”或“ sysindexes”