Re: Preventing Indexes on Foreign Keys

In other words, if you don't want an index on the FK, then drop the referential integrity requirement. (remove the FK). After all why do you think it's called a Foreign Key? cheers j. dkosenko@monmouth.com (David Kosenko) on 03/21/98 12:18:11 PM Please respond to dkosenko@monmouth.com (David Kosenko) To: informix-list@iiug.org cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: Re: Preventing Indexes on Foreign Keys 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!