Menu
I've been told that if I foreign key two tables, that SQL Server will create something akin to an index in the child table. I have a hard time believing this to be true, but can't find much out there related specifically to this.
My real reason for asking this is because we're experiencing some very slow response time in a delete statement against a table that has probably 15 related tables. I've asked our database guy and he says that if there is a foreign key on the fields, then it acts like an index. What is your experience with this? Should I add indexes on all foreign key fields or are they just unnecessary overhead?
911
Yes, Innodb provide this. You can put a foreign key name after FOREIGN KEY clause or leave it to let MySQL to create a name for you. MySQL automatically creates an index with the foreign_key_name name.
Foreign keys do not create indexes. Only alternate key constraints(UNIQUE) and primary key constraints create indexes.
Yes, primary key is automatically indexed in MySQL because primary key, index, etc gets stored into B-trees. All engines including InnoDB as well as MyISAM automatically supports the primary key to be indexed.
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.
A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.
But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.
So it makes good sense to index any columns involved in a FK, but a FK per se is not an index.
Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".
55
Wow, the answers are all over the map. So the Documentation says:
A FOREIGN KEY constraint is a candidate for an index because:
Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. An index allows Microsoft® SQL Server™ 2000 to find related data in the foreign key table quickly. However, creating this index is not a requirement. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.
So it seems pretty clear (although the documentation is a bit muddled) that it does not in fact create an index.
23
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
