Drop or Remove Foreign Key Constraint in SQL Server 2008

Introduction:

Here I will explain how to create or remove / drop foreign key constraint in sql server.  Foreign key constraint in one table point to primary key in another table in sql serverand foreign key constraint is used to prevent the actions that would destroy the links between two tables.

Description:

In previous articles I explained Primary key constraint in sql server, foreign key constraint in sql server,Pass XML file as parameter from C# to SQL stored procedure, cursor example in sql server and many articles relating to SQL server. Now I will explain how to remove foreign key constraint in sql server.

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails like as shown below

UserID	UserName	FirstName	LastName
1	SureshDasari	Suresh	Dasari
2	PrasanthiDonthi	Prasanthi	Donthi
3	MaheshDasari	Mahesh	Dasari

After that create another table with Foreign Key and give name as SalaryDetails

SalID	Salary	UserID
1	10000	1
2	20000	2
3	30000	3

The column “UserID” is a primary key in UserDetails table

The column “UserID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation

Create Table SalaryDetails ( SalaryID integer , Salary integer, UserID varchar(50), PRIMARY KEY (SalaryID), CONSTRAINT fk_SalaryID FOREIGN KEY(UserID) REFERENCES UserDetails(UserID) )

Example of creating Foreign Key constraint during the time of ALTER TABLE

ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCESUserDetails(UserID)

To Drop Foreign Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

I hope it helps you….