Re: Indexing FOREIGN KEY
The rule of thumb is that a foreign key requires an index.
The function of a foreign key to enforce referential integrity would be so slow that it would be unusable, if indexes did not exist on both the foreign key and the primary key it references.
See also:
http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html
"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Starting with MySQL 4.1.2, such an index is created on the referencing table automatically if it does not exist."
Further down the page...
"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, the index on the foreign key is created automatically. In older versions, the indexes must be created explicitly or the creation of foreign key constraints fails."
Regards,
Bill K.
Subject
Written By
Posted
Re: Indexing FOREIGN KEY
May 19, 2006 02:37PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.