RE: Preventing Indexes on Foreign Keys

My data is too valuable not to put on FK. Therefore, I will live with the indexes on all those columns. I will be changing the way I create tables to make it easier to manage all the automatic indexes. This is the way I will be creating the tables(I have tested it and it worked great.): 1. Create the tables(without any PK or FKs). 2. Create indexes on the columns I'm going to put a PK or FKs (Note: Indexes for PK must be created as UNIQUE.) 3. Create PK first, then all FKs. This will at least allow me to name the indexes for the PK and FKs. >---------- >From: Peter Lancashire[SMTP:Peter.Lancashire.PL1@bayer.co.uk] >Sent: Monday, March 23, 1998 7:02 AM >To: informix-list@iiug.org >Subject: 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/ >