FOREIGN KEY in SQL SERVER

FOREIGN KEY in SQL SERVER

It is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Look at the following two tables:

·         StateInfo

·         CityInfo

--Creating a table stateInfo

create table StateInfo

(

StateCode int primary key,

StateName varchar(50) unique

)

--Create table  CityInfo  relationship with StateInfo (foreign key with StateCode)

Creating another table CityInfo

create table CityInfo

(

CityCode int primary key ,

CityName varchar(50) unique,

StateCode int foreign key references StateInfo(StateCode) on delete cascade

)

Query for Drop foreign key in the table.

ALTER TABLE CityInfo

DROP CONSTRAINT FK_CityInfo_StateInfo;

Query for Add foreign Key in table.

ALTER TABLE CityInfo

ADD FOREIGN KEY (StateCode) REFERENCES StateInfo(StateCode);

Note-: There are two type “Insert and Update Specification” in forgien key

1.    Delete Rule

§  ON DELETE CASCADE

§  ON DELETE SET NULL

§  ON DELETE SET DEFAULT

2.    Update Rule

·         ON UPDATE SET NULL

·         ON update SET DEFAULT

·         ON update  cascade

:- Cascade Rule with a foreign key

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Sql.

How to create the foregin key through the wizard diagram with step by step is given below: