Foreign keys and how they help the optimizer

I love foreign keys. In my opinion they just make perfect sense.

They keep my data “clean” and consistent. Whenever two tables need to correspond with each other, I like to make sure that they do. For me as a DBA, the easiest and most intuitive way to do that is to create a FK constraint. Some of you (maybe even most of you) will say that FKs can be replaced by the application. Let it do all of the “dirty work” of making sure that the data in our DB makes sense business wise. But if there’s something I hate, it’s cleaning up after some programmer’s bug. Data fixes are a pain in the ***.

However, there is another reason why FKs are important. They can help the optimizer make better and more efficient decisions when creating an execution plan. To explain exactly how and why, let first see what a FK is.

What’s a Foreign Key ?

A foreign key represents a relation between two tables in a database. The relation is determined by matching the value in a column (or combination of columns) in the referencing table to the values in the columns of the referenced table. The values in the referenced table must be unique, either a primary key or a unique constraint.

These two conditions make sure two things will always be true:

  1. For every value in the FK there is a matching record in the referenced table

  2. There is only one match

How does it help the optimizer ?

Naturally, the optimizer knows how to take these two trues into consideration when creating the execution plan. To demonstrate I’ve created two tables with a FK relation between them.

The NameList table contains 100 names and Ids with the Id as a primary key for the table.

Transact-SQL

-- Create the list tables CREATE TABLE dbo.NameList ( Id INT NOT NULL , Name VARCHAR(10) NOT NULL , CONSTRAINT pk_NameId_c PRIMARY KEY CLUSTERED (Id) ); INSERT INTO dbo.NameList(Id, Name) SELECT TOP 100 ROW_NUMBER()OVER(ORDER BY T1.name) , 'Name_'+CAST(ROW_NUMBER()OVER(ORDER BY T1.name) AS VARCHAR(4)) FROM sys.all_columns AS T1 CROSS JOIN sys.all_columns AS T2; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

-- Create the list tables

CREATE TABLE

dbo.NameList

(

Id INT NOT NULL ,

Name VARCHAR(10) NOT NULL ,

CONSTRAINT pk_NameId_c PRIMARY KEY CLUSTERED (Id)

);