SQL FOREIGN KEY CONSTRAINT Syntax

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