SQL FOREIGN KEY CONSTRAINT Syntax

In this step by step SQL Tutorial I will show you how to using SQL ADD FOREIGN KEY CONSTRAINT. For the purpose of this tutorial I will use SQL Server 2012.

Other Versions of SQL Server:

• SQL Server 2012
  • This blog post has been written using SQL Server 2012
• SQL Server 2008 / R2
  • The examples we used should work in SQL Server 2008 and R2
• SQL Server 2005
  • We suspect that this will work in SQL Server 2005 as well

Who is it for?

• People who would like to learn basics of using FOREIGN KEY Constraint
• Those who are preparing for Microsoft certifications and need to cover this topic.
  • Exam Guide - 70-461 Querying SQL Server 2012

Contents

• ALTER TABLE ADD FOREIGN KEY Constraint Introduction
• ALTER TABLE ADD FOREIGN KEY Constraint Example

ALTER TABLE ADD FOREIGN KEY Constraint Introduction

Foreign Key constraint is used to ensure that a value in the Foreign Key column exists also in Primary Key field in Parent Table. For example if you have new product with code ABC123 than you should not be able to sell it until it entered on the system in Product table (Parent table). Foreign Key constraint check if value 'ABC123' exists in Parent Table in Primary Key and if it does not then it will fail the operations (insert or update).

FOREIGN KEY constraint  syntax:

ALTER TABLE  TableName 
ADD CONSTRAINT FK_ConstraintName 
FOREIGN KEY  (ForeignKey_ColumnName)
REFERENCES PrimaryKey_TableName (PrimaryKey_ColumnName)

Foreign Key Constraints are added in tables where FK column exists (Child Table) using ALTER TABLE ADD CONSTRAINT. You need to specify your column using FOREIGN KEY and related to table that contains Primary Key for this Foreign Key using REFERENCES and specifying PirmaryKey Table Name (Parent Table) and Pirmary Key Column.

In the Object Explorer I open AdventureWorksDW2012 database then I expand Tables folder and find table which I will use to add  FOREIGN KEY constraint. In this case it is dbo.FactInternetSales table. 

ALTER TABLE ADD FOREIGN KEY Constraint Example

I open new query and type my code to create foreign key constraint (see below) and run my query.

You can see that I add Foreign Key to FactInternetSales by using ALTER TABLE on this table. Add constraint name and specify field name using FOREIGN KEY (CustomerKey) and then use REFERENCE to say that CustomerKey is related to CustomerKey Primary key in DimCustomer

In the Object Explorer, Keys folder (NOT Constraints folder) I can see that new foreign key constraint is added. 

I hope that help 

Katie