SQL Server - FOREIGN Key Constraint
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.
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
Example of Foreign Key
2) Add Foreign key constraint on table using table design.
Step 1 :
Step 2 :
Step 4 :
Step 5 : Save table.
Step 6 : Refresh the table list and expand key folder from tblStudentMarks table, so it will show the relationship.
Let's see how data get store of primary key and foreign key.
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.
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.