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

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!