Re: Preventing Indexes on Foreign Keys

David Kosenko wrote: > > LT J Koermer <jkoermer@LSU.USCG.mil> offerred: > :Hi everyone, > :I have several tables that I create with Foreign Keys on columns, but I > :do NOT want Informix to automatically put an index on some of these > :columns. Is there a way to prevent this? > > No, there is not. If you have a FK defined on a column, and index must be > present. Note that you can create your own index BEFORE defining the FK > constraint, and Informix will use your index if it is the same as what it would > create for the FK. > > As to why this must be, remember that a FK defines a relationship between two > tables that must be enforced. A table with a FK cannot have a row inserted into > it with a value for that FK that does not exist in the referenced table. The > index on the primary key (PK) is needed to do a quick lookup to verify this. > Going in the other direction, the referenced table cannot have a row deleted > when the primary key value is contained in a table referencing that primary > key. A need to test this condition quickly must be available, so an index on > the FK is essential. Without it, whenever you wanted to delete a row from a > table with a PK, it would have to do a SEQUENTIAL SEARCH on every table with a > FK referencing this table to determine if any rows contained that PK value. > Think of the performance hit there! You have to trade between the database ensuring referential integrity and speed. My data are too valuable not to have enforced foreign keys. How about yours? With a bit of luck, the optimizer will ignore the index on small tables. There's still the insert and update problem in the table with the FKs as maintaining the index can be slow (well, not that slow). I agree it would be nice to have the option for lookup tables which are essentially fixed - they don't change so you never need to look up their foreign keys. On the rare occasiona you do, the optimizer might make you a temporary index. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.co.uk/