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.
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.
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