Are foreign keys automatically indexed in SQL Server?

Question

Will the following SQL statement automatically create an index in Table1.Table1Column or should it be explicitly created?

The core of the database is SQL Server 2000

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

Answer 1

SQL Server will not automatically create an index for a foreign key. Also from MSDN:

A FOREIGN KEY constraint does not have to be associated only with a MAIN KEY; a constraint in another table; it can also be defined to refer to columns of a UNIQUE constraint in another table. FOREIGN KEY constraint may contain null values; however, if any column of the compound FOREIGN KEY constraint contains null values, checking all the values ​​that make up the FOREIGN KEY constraint is skipped. To ensure that all values ​​of the FOREIGN composite KEY constraint are checked, specify NOT NULL on all participating columns.

Answer 2

When I read Mike's question, he asks if FK Constraint will create an index in the FK column in the table in which FK is located (Table 1). The answer is no, and in general. (for purposes of limitation), there is no need to do this. Column (s) defined as "TARGET" restrictions, on the other hand, must be a unique index in the referenced table, either a primary key or an alternate key. (unique index) or constraint creation status.

(EDIT: added for explicit consideration of the comment below) In particular, while ensuring data consistency to restrict the foreign key. an index can affect DRI Constraint performance only for deleting a row or rows on the FK side. When using a constraint during insert or update, the processor knows the value of FK and must check for the presence of a row in the table referenced by the PK side. There is already an index. When deleting a row on the PK side, he must make sure that there are no rows on the FK side. In this case, the index may be slightly useful. But this is not a common scenario.

In addition, in some types of queries, while the query processor must find records on many sides of the connection that use this foreign key column. connection performance increases when an index exists for this foreign key. But this condition is typical for the use of the FK column in the join request, and not for the existence of a foreign key constraint ... It does not matter if the other side of the join is PK or just some other arbitrary column. Also, if you need to filter or order query results based on this FK column, the index will help ... Again, this has nothing to do with limiting the foreign key in this column.

Answer 3

No, creating a foreign key in a column does not automatically create an index in that column.

Failure to index a foreign key column will cause this table to be checked every time a record is deleted from the table with the (parent) indication.

In this example diagram:

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

OrderDetail will be checked every time a record is deleted in the MasterOrder table.

Because indexes affect insertions, updates, and deletes, you cannot always carry foreign service keys if records in the parent table are never deleted.