Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-06-05 : 00:41:54
|
| I was surprised to learn tonight that foreign keys aren't indexes. Many of the articles seem to apply to SQL Server 2000. Is that still the case with 2005 and 2008? Do I need to add indexes for foreign keys in my tables? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-05 : 01:28:33
|
| Foreign keys are not indexes in any version of SQL Server.As a general rule, you should have an index on the child table that matches the columns in the foreign key constraint.CODO ERGO SUM |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-05 : 09:50:30
|
| A foreign key requires a primary key or unique constraint/index on the referenced (parent) table. The constraints are maintained with unique indexes in SQL Server. The referencing (child) table doesn't require an index but as Michael said it improves performance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-06 : 12:35:25
|
| "I was surprised to learn tonight that foreign keys aren't indexes"Lets assume you always will have such an index. If SQL created it for you it would preclude you having an index with additional column, for example. And what fill-factor, padding etc. would it use?Thus you have to create the index yourself, but you have choice over exactly how the index is "shaped".I do think there ought to be a "validation" or "Lint" tool that warns you if you forget an index on a FKey though ... |
|
|
|
|
|
|
Foreign keys need indexes?