Query to check if primary key exists on the table in informix

Question

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;

I get this error:

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

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

Answer 1

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

where the constrtype :

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

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";

where idxtype:

idxtype CHAR(1) Index type:
U = Unique
D = Duplicates allowed
G = Nonbitmap generali
g = Bitmap generalized
u = unique, bitmap
d = nonunique, bitmap

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

Answer 2

To see the table schema for table asamembr you can use dbschema in command line:

dbschema –d yourdbname –t asamembr

See here for some examples

Or you can go to dbaccess yourdbname > Table > Info > asamembr and see the table information there, but i prefer using dbschema because it will show you everything in one place.

About your error, the referenced column (or set of columns when you use the multiple-column constraint format, which is your case ) must have a unique or primary-key constraint. And in your case that looks like it isn't the case. See more info here