In this tutorial you will how to create foreign key in SQL table, create relation between primary key and foreign key in SQL table.
A foreign key in sql is a key used to link two SQL tables together known as sql foreign key relation.
The table holding the foreign key reference is called the child table
Let’s look at the picture below
"tbClassTeacherMap" is having a foreign key column called "TeacherId" ,
and the Foreign key constraint name
So far you have understood what is Foreign key column, and what is Foreign Key Constraint, Now you learn how to create Foreign Key Constraint.You can create Foreign Key two ways
At the time of Table Creation
CREATE TABLE ClassStudentMap ( CSId int not null identity(1,1) PRIMARY KEY, ClassId int NOT NULL, StudentId bigint NOT NULL, CONSTRAINT FK_ClassStudentMap_Student FOREIGN KEY (StudentId) REFERENCES tbStudent(StudentId) );
You also can Alter the Table to add FOREIGN KEY reference
alter TABLE ClassStudentMap add CONSTRAINT FK_ClassStudentMap_ClassRoom FOREIGN KEY (ClassId) REFERENCES tbClassRoom(crId)
You can drop any foreign key constraint just by altering the child table, simply drop the foreign key constraint
ALTER TABLE [TableName] DROP CONSTRAINT FK_ConstName; ALTER TABLE ClassStudentMap DROP CONSTRAINT FK_ClassStudentMap_ClassRoom;
To understand the relationship between foreign key and primary key, You should also look at SQL Primary Key