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]
)
)
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.
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.
Unlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index