Foreign key

Foreign key is used for to link two table together.

1) Foreign key is a field in the table that is primary key / unique Key in another table.

2) Foreign key can allow multiple null value.

3) when we create foreign key on column, it don't automatically create an index (clustered or non-clustered). we have to manually create an index on foreign key.

4) We can have more than one foreign key in a table.

5) Foreign key constraints are not enforced on temporary tables.

6) You create a relationship between two tables when you want to associate rows of one table with rows of another.

foreign key can be null -Yes.

orphan record - the parent rows are not present but child rows are present.

SELECT  C.* FROM  ChildTable C LEFT JOIN MasterTable M   ON M.ID = C.MasterID WHERE   M.ID IS NULL

How to Create Foreign Key

Create table Dept 

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null

)

1. while creating Table

Create table Emp

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

DeptId int null Foreign Key references Dept(Id)

)

2. while creating Table using constraint

Create table Emp

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

DeptId int,

Constraint fk_deptid Foreign key(DeptId) references Dept(id)

)

3. After Table creation 

Create table Emp

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

DeptId int

)

Alter table Emp

Add Constraint fk_deptid Foreign key(DeptId) references Dept(id)

Self Referencing table- refers to table itself.

Create table Dept 

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

ParentId int Null Foreign key references Dept(Id)

)

Drop Foreign Key

Alter Table dbo.Customerobj

Drop Constraint fk_deptid

Update Foreign Key Table (child Table) If We Disable Constraint then we can execute update query.

Alter Table TableName

Nocheck Constraint Constraint_Name

Clause related Query (On Delete On Update)

Create table Dept 

(

Id int  Primary Key,

Name varchar(50) Not Null

)

Create table Emp

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

DeptId int not null Foreign Key references Dept(Id)

)

Insert into Dept values(1,'Software')

Insert into Emp values('Sagar',1)

Select * from Dept 

Select * from Emp

Delete & Update Query On Primary Key table (parent) -

Update Dept Set id=5 where id=1

---Error----because On Update Cascade is not Set

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the REFERENCE constraint "FK__Emp__DeptId__29572725". The conflict occurred in database "Practice", table "dbo.Emp", column 'DeptId'.

The statement has been terminated.

Delete From Dept Where id=1

---error----because On Delete Cascade is not Set

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint "FK__Emp__DeptId__29572725". The conflict occurred in database "Practice", table "dbo.Emp", column 'DeptId'.

The statement has been terminated.

Solution

Create table Emp

(

Id int identity(1,1) Primary Key,

Name varchar(50) Not Null,

DeptId int not null Foreign Key references Dept(Id) on delete cascade on update cascade 

)

update Customer set id=5 where id=1-------Possible---Due to On Update clause

Delete From Customer Where Id=5-----------Possible---Due to On Delete clause

clause

ON DELETE

Optional one. It specifies what to do with the child data when the parent data is deleted. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

ON UPDATE

Optional one. It specifies what to do with the child data when the parent data is updated. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

NO ACTION

when the parent data is deleted or updated then no action is performed with the child data.

CASCADE

when the parent data is deleted or updated then child data is either deleted or updated.

SET NULL

when the parent data is deleted or updated then the child data is set to NULL.

SET DEFAULT

when the parent data is deleted or updated then the child data is set to their default values.

Update Query On Foreign Key Table (child)

Update Emp Set DeptId =5 Where DeptId =1

---Error

Msg 547, Level 16, State 0, Line 31

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Emp__DeptId__36B12243". The conflict occurred in database "PracticeDB", table "dbo.Dept", column 'Id'.

The statement has been terminated.

Solution----Disable Constraint

Alter Table Emp

Nocheck Constraint FK__Emp__DeptId__36B12243

-------------Enable Constraint

Alter Table Bill

check Constraint FK__Emp__DeptId__36B12243

how to disable foreign key constraint ?

Alter Table Bill

NOCHECK constraint FK__Emp__DeptId__36B12243

Note - We can disable foreign key constraint and Check Constraint.

Composite Foreign Key

We can add multiple columns in Foreign key but referencing table must need Composite primary key.

CREATE TABLE dbo.Customer

(

Id INT NOT Null unique,

AccountNo INT NOT NULL,

FirstName VARCHAR(100) NOT NULL,

LastName VARCHAR(100) NOT NULL,

City VARCHAR(50)

Primary Key (Id,AccountNo)

)

CREATE TABLE dbo.CustomerCreditCard

(

Id INT NOT Null Identity(1,1),

IdCC Int Not Null,

AccountNo INT Not Null

)

Alter Table dbo.CustomerCreditCard

Add Constraint FK_IDCC_AccNo_CustomerCreditCard Foreign key(IdCC,AccountNo) references dbo.Customer(Id,AccountNo)

List of foreign keys in sql