3

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 ?

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168

3 Answers3

5

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
  • 30,688
  • 6
  • 69
  • 111
  • 1
    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 Jul 28 '09 at 21:23
5

No, it is a constraint, not an index.

see Are foreign keys indexed automatically in SQL Server?

Community
  • 1
  • 1
Donald Byrd
  • 7,668
  • 4
  • 33
  • 50
0

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

Arnkrishn
  • 29,828
  • 40
  • 114
  • 128