How do I get constraint details from the name in Informix?

Question

When programming a large transaction (lots of inserts, deletes, updates) and thereby violating a constraint in Informix (v10, but should apply to other versions too) I get a not very helpful message saying, for example, I violated constraint r190_710. How can I find out which table(s) and key(s) are covered by a certain constraint I know only the name of?

Answer 1

From Informix Guide to SQL: Reference it appears you should look at the system catalog tables SYSCONSTRAINTS and SYSINDICES.

Answer 2

Tony Andrews suggested (pointing to a different end-point for the URL):

From Informix Guide to SQL: Reference it appears you should look at the system catalog tables SYSCONSTRAINTS and SYSINDICES.

The Informix system catalog is described in that manual.

The SysConstraints table is the starting point for analyzing a constraint, most certainly; you find the constraint name in that table, and from there you can find out the other details.

However, you also have to look at other tables, and not just (or even directly) SysIndices.

For example, I have a lot of NOT NULL constraints on the tables in my database. For those, the constraint type is 'N' and there is no need to look elsewhere for more information.

A constraint type of 'P' indicates a primary key; that would need more analysis via the SysIndexes view or SysIndices table. Similarly, a constraint type of 'U' indicates a unique constraint and needs extra information from the SysIndexes view or SysIndices table.

A constraint type of 'C' indicates a check constraint; the text (and binary compiled form) of the constraint is found in the SysChecks table (with types 'T' and 'B' for the data; the data is more or less encoded with Base-64, though without the '=' padding at the end and using different characters for 62 and 63).

Finally, a constraint type of 'R' indicates a referential integrity constraint. You use the SysReferences table to find out which table is referenced, and you use SysIndexes or SysIndices to establish which indexes on the referencing and referenced tables are used, and from that you can discover the relevant columns. This can get quite hairy!

Answer 3

From surfing at www.iiug.org (International Informix Users Group) i found the not-so-easy solution.

(1) Get referential constraint data from the constraint name (you can get all constraints for a table by replacing "AND sc.constrname = ?" by "AND st.tabname MATCHES ?"). This statement selects some more fields than necessary here because they might be interesting in other situations.

SELECT si.part1, si.part2, si.part3, si.part4, si.part5, 
    si.part6, si.part7, si.part8, si.part9, si.part10, 
    si.part11, si.part12, si.part13, si.part14, si.part15, si.part16, 
    st.tabname, rt.tabname as reftable, sr.primary as primconstr, 
    sr.delrule, sc.constrid, sc.constrname, sc.constrtype, 
    si.idxname, si.tabid as tabid, rc.tabid as rtabid 
FROM 'informix'.systables st, 'informix'.sysconstraints sc, 
     'informix'.sysindexes si, 'informix'.sysreferences sr, 
     'informix'.systables rt, 'informix'.sysconstraints rc 
WHERE st.tabid = sc.tabid 
  AND st.tabtype != 'Q' 
  AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*' 
  AND rt.tabid = sr.ptabid 
  AND rc.tabid = sr.ptabid
  AND sc.constrid = sr.constrid 
  AND sc.tabid = si.tabid 
  AND sc.idxname = si.idxname 
  AND sc.constrtype = 'R' 
  AND sc.constrname = ?
  AND sr.primary = rc.constrid 
ORDER BY si.tabid, sc.constrname

(2) Use the part1-part16 to determine which column is affected by the constraint: the part[n] containing a value different from 0 contains the column number of the used column. Use (3) to find the name of the column.

If constrtype is 'R' (referencing) use the following statement to find the parts of the referencing table:

SELECT part1, part2, part3, part4, part5, part6, part7, part8, 
    part9, part10, part11, part12, part13, part14, part15, part16 
FROM 'informix'.sysindexes si, 'informix'.sysconstraints sc 
WHERE si.tabid = sc.tabid 
AND si.idxname = sc.idxname 
AND sc.constrid = ? -- primconstr from (1)

(3) the tabid and rtabid (for referencing constraints) from (1) can now be used to get the columns of the tables like that:

SELECT colno, colname 
FROM 'informix'.syscolumns 
WHERE tabid = ? -- tabid(for referenced) or rtabid(for referencing) from (1)
  AND colno = ? -- via parts from (1) and (2)
ORDER BY colno

(4) If the constrtype is 'C', then get the check information like this:

SELECT type, seqno, checktext
FROM 'informix'.syschecks
WHERE constrid = ? -- constrid from (1)

Quite hairy indeed