I have a table T with a primary key id and foreign key f. Is f automatically indexed when it is specified as a foreign key? Do I need explicitly add an index for f ?
+5
A:
No index is created so yes, you need add explicitly add an index.
Edited to add... I probably ought to add that the source table/column for the data in table T must have a unique index. If you try and make an FK to a column that isn't a unique index (either as a PK or with a UNIQUE constraint), the FK can't be created.
Chris J
2009-07-28 20:35:11
Just wanted to add, you need these indexes most of the time for performance when you join the parent and child tables. It should be a routine part of creating your foreign key to also create the index at that time unless there is a compelling reason not to. I can think of very few times when I would not want an index on a foreign key field.
HLGEM
2009-07-28 21:23:37
+5
A:
No, it is a constraint, not an index.
see http://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server
ongle
2009-07-28 20:37:15
A:
Hi
In case of the foreign key constraint, the foreign key f in table T would be a primary key in the referenced table say T2. In SQL Server a clustered index would be automatically created when T2 got created.
cheers
Andriyev
2009-07-28 20:42:25