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 |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2008-05-08 : 05:17:33
|
| Hello,A quick question concerning foreign keys. I have been creating a database using the database diagram view in sql server management studio, and have an issue when creating foreign key relationships. When I specify a relationship in this view I imagine what is created is simply a constraint between a unique field in one table and the a foreign key in another. My issue is, is it better to index all foreign keys? If this is the case, is there a way to automate this so I don't have to manually create an index for every foreign key?Thanks,Mike |
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-08 : 05:24:58
|
| It is not desirable to index all foreign keys, only those that would benefit from an index. As such you wouldn't want something that automatically adds them. |
 |
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-05-08 : 05:55:09
|
| Okay, when is it not preferable to index a foreign key? From what I understand, indexing will speed up testing the foreign key constraint and also speed up joins. What are the disadvantages of indexing all foreign keys? Thanks. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-08 : 06:09:51
|
| The usual rules of indexes still apply. If you have a table with 3 million rows, related to a lookup table containing about a dozen rows then the index will not help in the join. The disadvantages are maintaining the index during inserts, updates and deletes and managing the fragmentation. I believe that the engine can use the index of the referenced key, not the foreign key itself as this would make no sense, to enforce the constraint. Since you can only reference a unique index or primary key, both of which the engine enforces using indexes anyway, the indexing is handled for you. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-08 : 06:17:27
|
| To expand:Broadly speaking, there are two types of foreign key. Firstly, those that, together with other foreign keys, compose part of the table's natural key. For example, a StudentCourses table would have references to Students and Courses. Both tables will be of a relatively significant size. The composite key for StudentCourses will be something like (StudentID, CourseID). This should be constrained as unique, so will automajically be indexed. One of these columns must come first so you *may* consider an index on the second of these columns. This does not mean that you will create one - just that you may.The second kind is the kind I mentioned earlier. If the foreign key does not compose part of the natural key then it is likely from a lookup table and likely again that this lookup table will have proportionally much fewer rows. In this case an index is redundant. |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-05-08 : 09:34:51
|
| Yes, that makes sense to me. Looking at the database I've created foreign keys do seem to fall in either of those groups so are already optimised for the reasons you discussed. However, I am a little lost on your point saying "One of these columns must come first", I don't exactly know what you mean by this. Thanks very much, that explanation was a lot of help. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-08 : 10:04:47
|
Thank you - I'm glad it was of use  Anyhoo - for that table the PK must be either (StudentID, CourseID) or (CourseID, StudentID). Only one of the two columns can come first (or be left most) in the index. So (StudentID, CourseID) will join wonderfully to Students but is not much use joining to Courses. The converse is true for (CourseID, StudentID). So - if your pk (and therefore index) is (StudentID, CourseID) and you find yourself writing queries joining to Courses without joining to students then you might want to index CourseID. Again, I need to stress the *might* - there is no hard and fast rule - you need to suck it and see. Of course, all the other interesting variables you need to consider with all indexes come into play also (clustered, nonclustered, covering, how many rows the query{ies} is{are} likely to return etc). |
|
|
|
|
|
|
|
|
Foreign Key Indexes