Menu
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
Foreign keys do not create indexes. Only alternate key constraints(UNIQUE) and primary key constraints create indexes. This is true in Oracle and SQL Server.
SQL Server will not automatically create an index on a foreign key. Also from MSDN: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
Yes, but only on innodb. Innodb is currently the only shipped table format that has foreign keys implemented.
Apparently an index is created automatically as specified in the link robert has posted.
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.) index_name, if given, is used as described previously.
InnoDB and FOREIGN KEY Constraints
Yes, see InnoDB and FOREIGN KEY Constraints.
You don't get the index automatically if you do an ALTER TABLE (instead of CREATE TABLE), at least according to the docs (the link is for 5.1 but it's the same for 5.5):
[...] When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With