Re: Preventing Indexes on Foreign Keys

I wish there was! I have a couple of tables with 6 or 7 FK's with a pretty low cardinality (one has only 12 different values over some 200K rows). Just imagine the overhead of inserting new rows, had I actually declared the columns as FK's! Ciao, Marco _______________________________________________________________________________ Marco Greco, Catania, Italy marcog@linux.ctonline.it rem radioterapia +39 95 447828 fax 446558 Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.ctonline.it/~marcog Informix on Linux http://www.ctonline.it/~marcog/ifmxlinux.htm 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!