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
CREATE TABLE TestUniqueConstraint(id int)
GO
Add a unique constraint to the table
ALTER TABLE TestUniqueConstraint ADD CONSTRAINT ix_unique UNIQUE (id)
GO
Insert a value of 1, this should succeed
INSERT TestUniqueConstraint VALUES(1)
GO
Insert a value of 1 again, this should fail
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
CREATE TABLE TestForeignConstraint(id int)
GO
Add the foreign key to the table
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
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
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
CREATE TABLE TestUniqueIndex(id int)
GO
Add the unique index
CREATE UNIQUE NONCLUSTERED INDEX ix_unique ON TestUniqueIndex(id)
GO
Insert a value of 1, this should succeed
INSERT TestUniqueIndex VALUES(1)
GO
Insert a value of 1 again , this should now fail
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
CREATE TABLE TestForeignIndex(id int)
GO
Add the foreign key constraint
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
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
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
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
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
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
