If I have a table like so:
CREATE TABLE MyTable
(
Id INT PRIMARY KEY IDENTITY(1, 1),
FooId INT NOT NULL FOREIGN KEY REFERENCES Foo(Id),
Data NVARCHAR(10) NOT NULL
);
The following observations may be made:
A clustered index will be created on the primary key column Id
on the table MyTable
.
Also, as can be inferred, a clustered index will be created on the table Foo
for its primary key named Id
.
Question:
Will there also be created an index for the foreign key Foo.Id
on the table MyTable?
In other words, is a non-clustered index created implicitly for every foreign key on the dependent table.
In still other words, will the total number of indices created in this schema be 2 as follows:
Id
on the table MyTable
.Id
on the table Foo
.Or will there be the following 3 indices:
Id
on the table MyTable
.Id
on the table Foo
.Foo(Id)
on the table MyTable
.My question pertains to Microsoft SQL Server 2014.
Defining a foreign key constraint does not create the underlying index. A constraint only put a limit on what data can be inserted into the column. Adding a non-clustered index to the Foreign Key Column in the child table can increase query performance by removing Table or Index Scans with Index Seeks.
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
It automatically creates a UNIQUE index for a PRIMARY KEY or UNIQUE constraint, and requires that a UNIQUE index be present for the referenced end of a foreign key relationship, but does nothing automatically for the referencing end, though it's often a good idea to make one yourself.
Foreign key is a field in the table that is Primary key in another table. Foreign key can accept multiple null value. Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
No, it is not created automatically. It is a good practice to create it manually:
The Benefits of Indexing Foreign Keys
Unlike primary key constraints, when a foreign key constraint is defined for a table, an index is not created by default by SQL Server.
However, it's not uncommon for developers and database administrators to add them manually
CREATE TABLE MyTable(
Id int PRIMARY KEY IDENTITY(1, 1),
FooId int NOT NULL FOREIGN KEY REFERENCES Foo(Id),
Data nvarchar(10) NOT NULL,
);
exec sp_helpIndex 'MyTable'
index_name index_description index_keys
PK__MyTable__3214EC0742A69968 clustered, unique, primary key located on PRIMARY Id
Explicit index creation:
CREATE TABLE MyTable (
Id int PRIMARY KEY IDENTITY(1, 1),
FooId int NOT NULL FOREIGN KEY REFERENCES Foo(Id),
Data nvarchar(10) NOT NULL,
INDEX FK_FooId nonclustered(FooId) -- inline syntax
);
exec sp_helpIndex 'MyTable'
index_name index_description index_keys
FK_FooId nonclustered located on PRIMARY FooId
PK__MyTable__3214EC0779B032FB clustered, unique, primary key located on PRIMARY Id
db<>fiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With