Foreign Key Constraint in SQL Server

Foreign Key Constraint:

We have an employee table and under the employee table  and consider we have a gender column. But in Gendar there are three values only: Male,Female,and Unknown. So in this case we don’t want the user to add some other values(unrelated data)in that column. That time foreign key came into the picture.

• It is nothing but one column from the table pointing to the primary key in the column from another table.

• Using it we achieve two table relations but both columns data type will be required the same.

• Used to Prevent inserting invalid data  into the column.

• The value that we insert into the foreign key column, has to be one of the values contained in the table it points to.

So first we need to create a gendar table and insert value into it.

Create Table tblGender(ID int Primary Key,Gender varchar(50))

insert into tblGender values(1,'Male')

insert into tblGender values(2,'Female')

insert into tblGender values(3,'Unknown')

Here we don’t have a gender column in our employee1 table. So first we add a gender column and then add a foreign key constarint

alter table employee1 add genderID int

Add Constraint

Syntax:
Alter table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Example

alter table employee1 add constraint empployee1_genderID_FK foreign key (gendarID) references tblGender (ID)

add foreign key constraints at the time of creating the table

Here we are going to create a new student table with id,name and genderId foreign key column. Let's go..

create table stud (id int primary key,name varchar(50),genderId Int,CONSTRAINT stud_id_FK foreign key (genderId) references tblGender(ID))

insert into stud values(1,'Sarah shaha',1)

insert into stud(id) values(13)

Once we insert the data and check the inserted data we see there is null value under gendarId column that value is not present in primary table i.t tblgender.

select * from stud

It means Forign key columns allow us to insert null values that are not related to the primary key column. 

To avoid this we need to provide it some default value that we achieve using default constraint. That we check in the next lecture.

Drop Foreign Key Constraint

Syntax:

alter table tbl_name drop CONSTRAINT foreignKeyTblName_foreignKeyColumnName_FK

Example

alter table stud drop CONSTRAINT stud_id_FK

If we try to drop a table that is pointing to foreign key table then it will not allow us to drop the table.

drop table tblGender //Could not drop object 'tblGender' because it is referenced by a FOREIGN KEY constraint.

Also if we try to drop a foreign key column directly it will not be allowed. We will see two errors.

alter table employee1 drop column gendarID

//1.The object 'empployee1_genderID_FK' is dependent on column 'gendarID',

  2.ALTER TABLE DROP COLUMN gendarID failed because one or more objects access this column.

To Drop Foreign key Column we need to follow below process

1.First remove constrint

    alter table employee1 drop CONSTRAINT empployee1_genderID_FK

2. Then Remove Column

alter table employee1 drop column gendarID