What is FOREIGN key constraint in SQL Server ?

Basically Foreign key constraint is use to create relation ship between two or more table.

Primary key of one table is become the Foreign key of another table. For example find below digram.

primary key and foreign key digram

In above digram primary key of student details become the foreign key of student marks and student address table.


1) Add FOREIGN key constraint on table by Query.
In below first example we create new table of tblStudentDetails and create StudentId as Primary Key because every student should have one unique key to process data in future.

In below second example we create the another table that is tblStudentMarks and create StudentId as Foreign key with reference of tblStudentDetails StudentId column.

Example of Primary Key

CREATE TABLE tblStudentDetails
(
Id INT IDENTITY (1,1), --Auto increment table row
StudentId INT NOT NULL PRIMARY KEY , --Student Unique Id through Database
StudentName VARCHAR (50),
City VARCHAR (50)
)

Example of Foreign Key

CREATE TABLE tblStudentMarks
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), --Auto increment table row
StudentId INT FOREIGN KEY REFERENCES tblStudentDetails(StudentId), --Primary key of tblStudentDetails is Foreign key of tblStudentMarks
Subject VARCHAR (50),
Marks INT
)


2) Add Foreign key constraint on table using table design.

Step 1 :


create foreign key in sql server

Step 2 :


create foreign key in sql server

Step 4 :


set foreign key relationship in sql server

Step 5 : Save table.


Step 6 : Refresh the table list and expand key folder from tblStudentMarks table, so it will show the relationship.

foreign key in sql server

Let's see how data get store of primary key and foreign key.


data example of primary key and foreign key in sql server

Note : If you observe the above example you will get that student Yogesh and Pankaj having two subject marks, where rest student having one subject marks.
This is called as one tow one and one to many relationship.



Now try to insert data of student who is not available in tblStudentDetails in second table that is tblStudentMarks.

The INSERT statement conflicted with the FOREIGN KEY constraint

Note : In above example we are trying to insert student id 20 in foreign key table but in primary table student with 20 Student Id not available.
Solution, First insert data in tblStudentMaster then insert data in tblStudentMarks with respected StudentId value.