Should Every SQL Server Foreign Key Have a Matching Index

Should every SQL Server foreign key have a matching index?

Yes it's a good practice, see here: When did SQL Server stop putting indexes on Foreign Key columns? scroll down to the Are there any benefits to indexing foreign key columns? section

Does a foreign key automatically create an index?

A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.

But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.

So it makes good sense to index any columns involved in a FK, but a FK per se is not an index.

Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".

Foreign Key to non-primary key

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

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.

So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.

Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?

Can a UNIQUE 'WHERE' index which allows multiple NULLS be used as the primary key for a foreign key relationship?

Can a UNIQUE 'WHERE' index which allows multiple NULLS be used as the
primary key for a foreign key relationship?

No. Unique filtered indexes cannot be be referenced by foreign keys.
Unique indexes (even with included columns, no filters though) can be referenced by foreign keys.

edit: for Nullability. Foreign keys can reference unique indexes/constraints with NULLable columns. Nullability is not a prerequisite for creating foreign keys. However, foreign keys are NOT checked for rows with at least one null value in any of the fk columns:

create table dbo.parent
(
id1 int null,
id2 int null,
constraint id1id2 unique(id1, id2)
);

insert into dbo.parent(id1, id2)
values(1, 1), (2, 2), (3, null);

go

create table dbo.child
(
childid int identity,
parentid1 int,
parentid2 int,
constraint fkparent foreign key(parentid1, parentid2) references parent(id1, id2)
)
go

insert into dbo.child(parentid1, parentid2)
values (1, 1), (2, 2) --ok
go

insert into dbo.child(parentid1, parentid2)
values (4, 4) -- fk violation, there is no 4,4 parent row
go

insert into dbo.child(parentid1, parentid2)
values (3, null) --do not get tricked here.... because the fk has a null value, the fk is NOT checked at all
go

--fk with one null value, fk is not checked at all 
insert into dbo.child(parentid1, parentid2)
values (100, null) -- but there is no 100, null parent row
go

select *
from parent;
select *
from child;

can we have a foreign key which is not a primary key in any other table?

Yes - you can have a foreign key that references a unique index in another table.

CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn)

ALTER TABLE dbo.YourChildTable
   ADD CONSTRAINT FK_ChildTable_Table
   FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)

Postgres and Indexes on Foreign Keys and Primary Keys

PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.

When Pg creates an implicit index it will emit a NOTICE-level message that you can see in psql and/or the system logs, so you can see when it happens. Automatically created indexes are visible in \d output for a table, too.

The documentation on unique indexes says:

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.

and the documentation on constraints says:

Since a DELETE of a row from the referenced table or an UPDATE of a
referenced column will require a scan of the referencing table for
rows matching the old value, it is often a good idea to index the
referencing columns. Because this is not always needed, and there are
many choices available on how to index, declaration of a foreign key
constraint does not automatically create an index on the referencing
columns.

Therefore you have to create indexes on foreign-keys yourself if you want them.

Note that if you use primary-foreign-keys, like 2 FK's as a PK in a M-to-N table, you will have an index on the PK and probably don't need to create any extra indexes.

While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT, UPDATE or DELETE. If the index is rarely used it may not be worth having.

Entity Framework Indexing ALL foreign key columns

In EF Code First, the general reason why you would model a foreign key relationship is for navigability between entities. Consider a simple scenario of Country and City, with eager loading defined for the following LINQ statement:

var someQuery = 
   db.Countries
     .Include(co => co.City)
     .Where(co => co.Name == "Japan")
     .Select(...);

This would result in a query along the lines of:

SELECT *
FROM Country co
INNER JOIN City ci
  ON ci.CountryId = co.ID
WHERE co.Name = 'Japan';

Without an Index on the foreign key on City.CountryId, SQL will need to scan the Cities table in order to filter the cities for the Country during a JOIN.

The FK index will also have performance benefits if rows are deleted from the parent Country table, as referential integrity will need to detect the presence of any linked City rows (whether the FK has ON CASCADE DELETE defined or not).

TL;DR

Indexes on Foreign Keys are recommended, even if you don't filter directly on the foreign key, it will still be needed in Joins. The exceptions to this seem to be quite contrived:

• If the selectivity of the foreign key is very low, e.g. in the above scenario, if 50% of ALL cities in the countries table were in Japan, then the Index would not be useful.

• If you don't actually ever navigate across the relationship.

• If you never delete rows from the parent table (or attempt update on the PK) .

One additional optimization consideration is whether to use the foreign key in the Clustered Index of the child table (i.e. cluster Cities by Country). This is often beneficial in parent : child table relationships where it is common place to retrieve all child rows for the parent simultaneously.

Is indexes slow if I want to use foreign key?

An index on a foreign key is used when:

• parent row is about to be deleted,
• parent's key (which is referenced by the foreign key) is about to be modified.

(It is not used when inserting or updating non-referenced fields of parent, nor when inserting, updating or deleting child.)

And of course, queries (especially JOINs) "on top" of foreign keys are quite common, and they may benefit from such indexes too.

So, as a rule of thumb, if you do the above operations often, consider having an index on the foreign key. And since every index requires maintenance, measure whether the price of maintenance justifies the speedup you get on these operations (as a rule of thumb, it usually does).

---

Related Topics