Re: Constraint indices

Art Said: > > Next, IDS will only use an exact index for a constraint index. That makes > sense. Yes, IDS can look up any appt_id in the compound index created for > the primary key, but that's not unique on appt_id in theory (or I suspect in > practice) so it's neither ideal nor efficient for the kind of lookups that > IDS will need to perform to maintain the FOREIGN KEY constraint. I > > Art S. Kagel Since appt_id is a foreign key it isn't supposed to be unique. Interestingly you can create a unique index for a foreign key constraint, which means that you will have a one to one relationship, and Informix will use that index. This is handy in some odd circumstances. I see no real theoretical problem with informix using only part of a compound index to implement a foreign key constraint. Nor do I see it to be a large technical challenge to implement. It just doesn't. This could be an enhancement. Sure people would use it incorrectly, what feature hasn't been abused by someone. The syntax to force the constraint to use a compound index instead of creating the perfect index could be alter table xxxx add constraint( foreign key (yyyy_id) references yyyy(id) on delete cascade using index xxxx_yyyy_fk constraint xxxx_fk ); The statement would of course fail if the yyyy_id column was not the lead column in the index. DBA's could then decide to make the trade off between space and speed. Using a compound index might be a tad slower but on a large or heavily updated table it might be better not to have the extra index. Let the DBA decide.