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

database constraints informix
user39039 picture user39039 · Nov 26, 2008 · Viewed 25.4k times · Source

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

Jonathan Leffler picture Jonathan Leffler · Nov 28, 2008

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!

Related questions

How do I add a custom CHECK constraint on a MySQL table?

I am having trouble with this table CREATE TABLE `Participants` ( `meetid` int(11) NOT NULL, `pid` varchar(15) NOT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`meetid`,`pid`), CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE CONSTRAINT `participants_…

Read More
Ruby on Rails: How do I add a not null constraint to an existing column using a migration?

In my Rails (3.2) app, I have a bunch of tables in my database but I forgot to add a few not null constraints. I've googled around but I can't find how to write a migration which adds not null to …

Read More
Limit SQL Server column to a list of possible values

How do I put a constraint on a column so that it can only contain the following values? What do you call this type of constraint? Allowed values: "yes", "no" or "maybe" Column Data Type: nvarchar(5) DBMS: SQL Server 2008

Read More