Cascading Referential Integrity Constraints in SQL Server through Query

Cascading Referential Integrity Constraints in SQL Server through Query.
|No Action,Set Null,Set default,Cascade|.

--Cascading Referential Integrity Constraints in SQL Server through Query

--The Cascading Referential Integrity Constraints in SQL Server that tell
--SQL Server to perform certain actions whenever a user attempts to delete or update a primary key

--NO ACTION
--This is the default action that SQL Server performs. This specifies that if an update or deletes statement affects rows in foreign key tables,
--then the action will be denied and rolled back. An error message will be raised
create Table Country
(
CountryId int primary key,
Name varchar(100)
)
Insert into Country values(1,'India')
Insert into Country values(2,'UK')
create Table Customer
(
CustomerId int primary key,
Name varchar(100),
CountryId int foreign key references Country(CountryId)
)
insert into Customer values(1,'Arun',1)
insert into Customer values(2,'Biju',null)
insert into Customer values(3,'Peter',2)
insert into Customer values(4,'Meera',1)
select * from Country
select * from Customer

--delete from Country where CountryId =1
--SET NULL
--when the primary key record is deleted or updated in the Primary key table that time foreign key column will be set to null
--we marked that column as allow null
--drop table Country
--drop table Customer
create Table Country
(
CountryId int primary key,
Name varchar(100)
)
Insert into Country values(1,'India')
Insert into Country values(2,'UK')
create Table Customer
(
CustomerId int primary key,
Name varchar(100),
CountryId int foreign key references Country(CountryId)
on delete set null
)
insert into Customer values(1,'Arun',1)
insert into Customer values(2,'Biju',null)
insert into Customer values(3,'Peter',2)
insert into Customer values(4,'Meera',1)
select * from Country
select * from Customer
--delete from Country where CountryId =1
--CASCADE
--when the primary key record is deleted or updated in the Primary key table that time foreign key column will also be deleted or updated
--drop table Country
--drop table Customer
create Table Country
(
CountryId int primary key,
Name varchar(100)
)
Insert into Country values(1,'India')
Insert into Country values(2,'UK')
create Table Customer
(
CustomerId int primary key,
Name varchar(100),
CountryId int foreign key references Country(CountryId)
on delete cascade
)
insert into Customer values(1,'Arun',1)
insert into Customer values(2,'Biju',null)
insert into Customer values(3,'Peter',2)
insert into Customer values(4,'Meera',1)
select * from Country
select * from Customer
--delete from Country where CountryId =1
--SET DEFAULT
--If a delete or update statement affects rows in a foreign key table, then -all rows containing those foreign keys are set to the default value.
create Table Customer
(
CustomerId int primary key,
Name varchar(100),
CountryId int default 2 foreign key references Country(CountryId)
on delete set default
)

Sql Query for jobs

Playlists:

Cte Links:

Stored procedure Links:

User Defined Functions Link:

Sql Query Interview Questions Link:

#sql #sqlserver #sqlab #sqlinjection #mssql #sqldeveloper #sqlsaturday
#sql #programming #database #sqlserver #coding #developer #programmer #software #datascience #code #computerscience #data #dataanalytics
#technology #computer #development #bigdata #backend #coder #codinglife #microsoft #sqldatabase #softwaredeveloper #informationtechnology #programminglife #softwaredevelopment #programmerslife #sqlforbeginners #sqltraining #sqlinterviewquestions #mssqlserver