This article will surely help you working with foreign key constraint in SQL SERVER.

The Problem

You will see how to work with foreign key constraint in SQL. After reading this, you will learn adding foreign key while creating new table or altering existing table. You will also learn how to remove foreign key from existing table. Let see step by step.

The Solution

Let's create few tables first. Create two tables and name it Users and Person respectively.

code

Adding Foreign Key - Create Table

Use CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY REFERENCES TABLE_NAME(COLUMN_NAME) to add foreign key constraint.

CREATE TABLE Users(ID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50))
CREATE TABLE Person(ID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50))

Adding Foreign Key On existing column - Alter Table

Use ADD CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY(COLUMN_NAME) REFERENCES TABLE_NAME(COLUMN_NAME) command. Create table ContactDetail1 with column UID and try to add foreign key constraint on this coulmn.

--ALTER TABLE TO ADD FOREIGN KEY

--It will create a table with column name UID in it
CREATE TABLE ContactDetail1(ID INT PRIMARY KEY, EmailID VARCHAR(50), ContactNo VARCHAR(50),
UID INT)

--This command will add foreign key on column UID
ALTER TABLE ContactDetail1 ADD CONSTRAINT FK_ContactDetail1_Users
FOREIGN KEY(UID) REFERENCES Users(ID)

Adding Foreign Key On new column - Alter Table

Use CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY REFERENCES TABLE_NAME(COLUMN_NAME) command.

--To add new column with foreign key on alter table
ALTER TABLE ContactDetail1 ADD PersonID int CONSTRAINT FK_ContactDetail1_Person_PersonID
FOREIGN KEY REFERENCES Person(ID)

Drop Constraint

Use DROP CONSTRAINT [CONSTRAINT_NAME] command.

--Drop constraint
ALTER TABLE ContactDetail1 DROP CONSTRAINT FK_ContactDetail1_Users

Hope this can help you. Use it and enjoy.