Are foreign keys indexed automatically in SQL Server?

Question

Would the following SQL statement automatically create an index on Table1.Table1Column, or must one be explicitly created?

Database engine is SQL Server 2000

       CREATE TABLE [Table1] (
. . .
            CONSTRAINT [FK_Table1_Table2] FOREIGN KEY 
            (
                [Table1Column]
            ) REFERENCES [Table2] (
                [Table2ID]
            )

        )

score 67 · Accepted Answer · edited Jun 09 '11 at 16:44

67

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 constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

edited Jun 09 '11 at 16:44

Charles Bretana

143,358
22
150
216

answered Nov 10 '08 at 20:05

jons911

1,836
15
19

All evidence seems to point to no automatic index on Table1Column. Created the index normally without SQL Server complaining. – Karmic Coder Nov 10 '08 at 21:23
9

What has the quoted text to do with the question or that statement that indexes are not automatically created? – John Jul 03 '17 at 21:38

Charles Bretana · Answer 2 · 2009-11-24T15:56:02.337

As I read Mike's question, He is asking whether the FK Constraint will create an index on the FK column in the Table the FK is in (Table1). The answer is no, and generally. (for the purposes of the constraint), there is no need to do this The column(s) defined as the "TARGET" of the constraint, on the other hand, must be a unique index in the referenced table, either a Primary Key or an alternate key. (unique index) or the Create Constraint statment will fail.

(EDIT: Added to explicitly deal with comment below -) Specifically, when providing the data consistency that a Foreign Key Constraint is there for. an index can affect performance of a DRI Constraint only for deletes of a Row or rows on the FK side. When using the constraint, during a insert or update the processor knows the FK value, and must check for the existence of a row in the referenced table on the PK Side. There is already an index there. When deleting a row on the PK side, it must verify that there are no rows on the FK side. An index can be marginally helpful in this case. But this is not a common scenario.

Other than that, in certain types of queries, however, where the query processor needs to find the records on the many side of a join which uses that foreign key column. join performance is increased when an index exists on that foreign key. But this condition is peculiar to the use of the FK column in a join query, not to existence of the foreign Key constraint... It doesn't matter whether the other side of the join is a PK or just some other arbitrary column. Also, if you need to filter, or order the results of a query based on that FK column, an index will help... Again, this has nothing to do with the Foreign Key constraint on that column.

"and generally there is no need to do this... " - that statement is incorrect I'm afraid. Indexing FK's can lead to better performance in many situations. — Mitch Wheat, Nov 24 '09 at 08:37
@Mike, The fact that you might want to use the same column in a join, (where an index could help) does not mean an index is required for the constraint. You might also want to use the index in a where clause predicate, where an index might help as well. Does this mean an index is required just for the FK side of the DRI Constraint? No, a FK on a constraint "generally" (except for deletes on the PK side) gains no benefit from an index. Using the FK colums in a join (different situation) or, for that matter, in a Where clause, is what garners benefits from the index. — Charles Bretana, Nov 24 '09 at 15:54
If there is no index on the foreign key then deleting rows on the referenced table will cause a table scan on the table with the foreign key. http://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys — David Sopko, Nov 24 '15 at 22:43
Simply joining the tables will cause a table scan if the foreign key is not indexed. For example: take the tables 'Purchases' and 'PurchasedItems'. For a particular Purchase joining on PurchasedItems will scan through every item ever purchased. — David Sopko, Mar 14 '19 at 00:55

David Sopko · Answer 3 · 2019-03-14T01:05:18.053

No, creating a foreign key on a column does not automatically create an index on that column. Failing to index a foreign key column will cause a table scan in each of the following situations:

Each time a record is deleted from the referenced (parent) table.
Each time the two tables are joined on the foreign key.
Each time the FK column is updated.

In this example schema:

CREATE TABLE MasterOrder (
   MasterOrderID INT PRIMARY KEY)

CREATE TABLE OrderDetail(
   OrderDetailID INT,
   MasterOrderID INT  FOREIGN KEY REFERENCES MasterOrder(MasterOrderID)
)

OrderDetail will be scanned each time a record is deleted in the MasterOrder table. The entire OrderDetail table will also be scanned each time you join OrderMaster and OrderDetail.

   SELECT ..
   FROM 
      MasterOrder ord
      LEFT JOIN OrderDetail det
       ON det.MasterOrderID = ord.MasterOrderID
   WHERE ord.OrderMasterID = @OrderMasterID

In general not indexing a foreign key is much more the exception than the rule.

A case for not indexing a foreign key is where it would never be utilized. This would make the server's overhead of maintaining it unnecessary. Type tables may fall into this category from time to time, an example might be:

CREATE TABLE CarType (
   CarTypeID INT PRIMARY KEY,
   CarTypeName VARCHAR(25)
)

INSERT CarType .. VALUES(1,'SEDAN')
INSERT CarType .. VALUES(2,'COUP')
INSERT CarType .. VALUES(3,'CONVERTABLE')

CREATE TABLE CarInventory (
   CarInventoryID INT,
   CarTypeID INT  FOREIGN KEY REFERENCES CarType(CarTypeID)
)

Making the general assumption that the CarType.CarTypeID field is never going to be updated and deleting records would be almost never, the server overhead of maintaing an index on CarInventory.CarTypeID would be unnecessary if CarInventory was never searched by CarTypeID.

even in your example the index is potentially not needed, you still might want to select and join CarInventory to CarType, right? And that might be a good reason to add the index anyways, right? — Jared Beach, Oct 08 '21 at 15:10

score 0 · Answer 4 · answered Jun 20 '22 at 05:18

According to: https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver16#indexes-on-foreign-key-constraints

Unlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index

Are foreign keys indexed automatically in SQL Server?

4 Answers4

Linked