This is day eight of the SQL Advent 2012 series of blog posts. In yesterday’s post SQL Advent 2012 Day 7: Lack of constraints we touched a little upon foreign key constraints but today we are going to take a closer look at foreign keys. The two things that we are going to cover are the fact that you don’t need a primary key in order to define a foreign key relationship, SQL Server by default will not index foreign keys

You don’t need a primary key in order to have a foreign key

Most people will define a foreign key relationship between the foreign key and a primary key. You don’t have to have a primary key in order to have a foreign key, if you have a unique index or a unique constraint then those can be used as well. Let’s take a look at what that looks like with some code examples

A foreign key with a unique constraint instead of a primary key
First create a table to which we will add a unique constraint after creation

T-SQL
1
2
CREATE TABLE TestUniqueConstraint(id int)
GO
CREATE TABLE TestUniqueConstraint(id int)
GO

Add a unique constraint to the table

T-SQL
1
2
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
GO
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
GO

Insert a value of 1, this should succeed

T-SQL
1
2
INSERT  TestUniqueConstraint VALUES(1)
GO
INSERT  TestUniqueConstraint VALUES(1)
GO

Insert a value of 1 again, this should fail

T-SQL
1
2
INSERT  TestUniqueConstraint VALUES(1)
GO
INSERT  TestUniqueConstraint VALUES(1)
GO

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint ‘ix_unique’. Cannot insert duplicate key in object ‘dbo.TestUniqueConstraint’. The duplicate key value is (1).
The statement has been terminated.

Now that we verified that we can’t have duplicates, it is time to create the table that will have the foreign key

T-SQL
1
2
CREATE TABLE TestForeignConstraint(id int)
GO
CREATE TABLE TestForeignConstraint(id int)
GO

Add the foreign key to the table

T-SQL
1
2
3
ALTER TABLE dbo.TestForeignConstraint ADD CONSTRAINT
    FK_TestForeignConstraint_TestUniqueConstraint FOREIGN KEY
    (id) REFERENCES dbo.TestUniqueConstraint(id) 
ALTER TABLE dbo.TestForeignConstraint ADD CONSTRAINT
	FK_TestForeignConstraint_TestUniqueConstraint FOREIGN KEY
	(id) REFERENCES dbo.TestUniqueConstraint(id) 

Insert a value that exist in the table that is referenced by the foreign key constraint

T-SQL
1
2
INSERT TestForeignConstraint  VALUES(1)
INSERT TestForeignConstraint  VALUES(1)
INSERT TestForeignConstraint  VALUES(1)
INSERT TestForeignConstraint  VALUES(1)

Insert a value that does not exist in the table that is referenced by the foreign key constraint

T-SQL
1
INSERT TestForeignConstraint  VALUES(2)
INSERT TestForeignConstraint  VALUES(2)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_TestForeignConstraint_TestUniqueConstraint”. The conflict occurred in database “tempdb”, table “dbo.TestUniqueConstraint”, column ‘id’.
The statement has been terminated.

As you can see, you can’t insert the value 2 since it doesn’t exist in the TestUniqueConstraint table

A foreign key with a unique index instead of a primary key
This section will be similar to the previous section, the difference is that we will use a unique index instead of a unique constraint

First create a table to which we will add a unique index after creation

T-SQL
1
2
CREATE TABLE TestUniqueIndex(id int)
GO
CREATE TABLE TestUniqueIndex(id int)
GO

Add the unique index

T-SQL
1
2
CREATE UNIQUE NONCLUSTERED INDEX ix_unique ON TestUniqueIndex(id)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_unique ON TestUniqueIndex(id)
GO

Insert a value of 1, this should succeed

T-SQL
1
2
INSERT  TestUniqueIndex VALUES(1)
GO
INSERT  TestUniqueIndex VALUES(1)
GO

Insert a value of 1 again , this should now fail

T-SQL
1
2
INSERT  TestUniqueIndex VALUES(1)
GO
INSERT  TestUniqueIndex VALUES(1)
GO

Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.TestUniqueIndex’ with unique index ‘ix_unique’. The duplicate key value is (1).
The statement has been terminated.

Now that we verified that we can’t have duplicates, it is time to create the table that will have the foreign key

T-SQL
1
2
CREATE TABLE TestForeignIndex(id int)
GO
CREATE TABLE TestForeignIndex(id int)
GO

Add the foreign key constraint

T-SQL
1
2
3
ALTER TABLE dbo.TestForeignIndex ADD CONSTRAINT
    FK_TestForeignIndex_TestUniqueIndex FOREIGN KEY
    (id) REFERENCES dbo.TestUniqueIndex(id) 
ALTER TABLE dbo.TestForeignIndex ADD CONSTRAINT
	FK_TestForeignIndex_TestUniqueIndex FOREIGN KEY
	(id) REFERENCES dbo.TestUniqueIndex(id) 

Insert a value that exist in the table that is referenced by the foreign key constraint

T-SQL
1
2
INSERT TestForeignIndex  VALUES(1)
INSERT TestForeignIndex  VALUES(1)
INSERT TestForeignIndex  VALUES(1)
INSERT TestForeignIndex  VALUES(1)

Insert a value that does not exist in the table that is referenced by the foreign key constraint

T-SQL
1
INSERT TestForeignIndex  VALUES(2)
INSERT TestForeignIndex  VALUES(2)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_TestForeignIndex_TestUniqueIndex”. The conflict occurred in database “tempdb”, table “dbo.TestUniqueIndex”, column ‘id’.
The statement has been terminated.

As you can see, you can’t insert the value 2 since it doesn’t exist in the TestUniqueIndex table

As you have seen with the code example, you can have a foreign key constraint that will reference a unique index or a unique constraint in addition to be able to reference a primary key

Foreign keys are not indexed by default

When you create a primary key, SQL Server will by default make that a clustered index. When you create a foreign key, there is no index created

Scroll up to where we added the unique constraint to the TestUniqueConstraint table, you will see this code

T-SQL
1
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)

All we did was add the constraint, SQL Server added the index behind the scenes for us in order to help enforce uniqueness more efficiently

Now run this query below

T-SQL
1
2
3
4
5
6
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestUniqueIndex','TestUniqueConstraint')
AND name IS NOT NULL
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestUniqueIndex','TestUniqueConstraint')
AND name IS NOT NULL

You will get these results

TableName	        IndexName	StorageType
---------------------   -----------     --------------
TestUniqueConstraint	ix_unique	NONCLUSTERED
TestUniqueIndex	        ix_unique	NONCLUSTERED

As you can see both tables have an index

Now let’s look at what the case is for the foreign key tables. Run the query below

T-SQL
1
2
3
4
5
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestForeignIndex','TestForeignConstraint')
SELECT OBJECT_NAME(object_id) as TableName,
name as IndexName, 
type_desc as StorageType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) IN('TestForeignIndex','TestForeignConstraint')

Here are the results for that query

TableName	      IndexName	StorageType
--------------------- --------- -------------
TestForeignConstraint	NULL	HEAP
TestForeignIndex	NULL	HEAP

As you can see no indexes have been added to the tables. Should you add indexes? In order to answer that let’s see what would happen if you did add indexes. Joins would perform faster since it can traverse the index instead of the whole table to find the matching join conditions. Updates and deletes will be faster as well since the index can be used to find the foreign keys rows to update or delete (remember this depends if you specified CASCADE or NO ACTION when you create the foreign key constraint)
So to answer the question, yes, I think you should index the foreign key columns

That is all for day eight of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap