Foreign keys and indexes
Posted in 1997
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?