Foreign keys and indexes

We use a mixture of foreign keys and application managed referential integrity, depending on our application. I prefer application managed RI on our larger databases because it is more efficient. We have some very large tables with highly duplicate values on the child columns that are participating in the RI. With indexes, there is a tremendous amount of overhead involved in maintaining the linked lists of rowids for the highly duplicate key values. If you choose to use RI, I would advise that you create your indexes in advance, giving you control over naming conventions, etc. In our case, we have over 250 sites, and we want to ensure that the index and constraint names are identical everywhere. If you let the engine name your structures, you won't have that consistency, plus, as you say, you are unable to access that index in an alter index to cluster statement. If you create the index in advance, Informix WILL use that index when you create your RI constraint later. There is one exception that I know of: If the index is a multicolumn index, say on columns a,b,c; and the RI is only on column a or columns a and b, Informix will NOT use that index. It will go ahead and create a new index for only the columns participating in the RI. NOT A NICE FEATURE. Mike Ballentine --------------------------( Forwarded letter 1 follows )--------------------- Date: Sunday, 6 April 1997 3:13pm From: itr1@cableol.co.uk Subject: Foreign keys and indexes Date:Sun, 6 Apr 1997 10:50:58 -0500 Reply-To: itr1@cableol.co.uk Organization: Cable OnLine Limited Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-Id: <news.36245> Does anyone have any thoughts on the use of foreign keys as opposed to indexes on an Informix database (7.1x on Solaris)? If I create a foreign key referential constraint then Informix understandably creates a system index to support the constraint. But this is not the same as an ordinary index and cannot be used to cluster the index at a later stage. The only way I can see of getting the clustering benefits and retaining the referential constraints is to create the index first and then add the constraint. (You can't do it the other way around because Informix will then stop you adding the index because there is already one in existence for the same fields from the referential constraint). Surely this is overkill because in effect I've now got two indexes. Do other people use referential constraints or do they rely on their coding to enforce these constraints?