查询以检查informix中的表上是否存在主键

Question

我们有一个名为asamembr的表，其中有两个字段：cust_code和mbrcode。

还有另一个表成员消息与外键具有相同的字段，但是当我使用以下查询创建约束时：

alter table 'informix'.messageclubmembership add constraint foreign key 
            (membership_number, member_code)
            references 'informix'.asamembr
            (cust_code, mbr_code) 
            on delete cascade 
            constraint fk_messageclubm926;

我收到此错误：

 Cannot find unique constraint or primary key on referenced table (informix.asamembr)

您能否在表asamembr的两个字段cust_code和mbr_code上告诉您如何查询主键？

Answer 1

首先寻找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) Code identifying the constraint type:
C = Check constraint
N = Not NULL
P = Primary key
R = Referential
T = Table
U = Unique

然后，检查索引列（查找与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)
      , (html" target="_blank">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) Index type:
U = Unique
D = Duplicates allowed
G = Nonbitmap generali
g = Bitmap generalized
u = unique, bitmap
d = nonunique, bitmap

在Informix在线手册中搜索“
sysconstraints”或“ sysindexes”