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

小编典典

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

sql

我们有一个名为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上告诉您如何查询主键？

阅读 241

2021-04-07

共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）标识约束类型的代码：
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”

2021-04-07