Question

I am using phpMyAdmin. In order to set up a foreign key constraint with InnoDB (under the "Relation View" link on the Structure tab) it appears that I need to add an index for the field to which I want to add the restraint. This obviously has an impact on performance of inserts/updates on the table, particularly if there are several constraints I want to add. Is it possible to specify a foreign key constraint or relational integrity in InnoDB without the need to create an Index for the required field?

Many thanks JS, London

Answer 1

From the MySQL reference manual:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.)

Answer 2

a foreign key contraint refers 1 key to another one. A foreign key is also an index.
So yes you need to index collumn (either with unique or with foreign key) to use them in a foreign key constraint.

There s 3 types of indexes available:
- primary key
- unique index
- non-unique index

友情链接