.jpg)
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:
