查询以检查informix中的表上是否存在主键 [英] Query to check if primary key exists on the table in informix

问题描述

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

We have a table called asamembr with two of its fields : cust_code and mbrcode.

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

There is another table membermessage with same fields as a foreign key but when I am using following query to create constraint:

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吗?

Can you please tell how to query if the primary key exists on the table asamembr on two fields cust_code and mbr_code?

推荐答案

首先查找PK的索引名称(pk_idx列)

First look for index name for the PK (pk_idx column)

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 =唯一

constrtype CHAR(1) Code identifying the constraint type:
C = Check constraint
N = Not NULL
P = Primary key
R = Referential
T = Table
U = Unique

然后，检查索引列(查找与PK约束相同的索引名称):

Then , check the index columns (look for the same index name of the PK constraint):

   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 =非唯一位图

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"

Search at Informix online manuals for "sysconstraints" or "sysindexes"

这篇关于查询以检查informix中的表上是否存在主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！