cascading Referential Integrity constraints||SQL||MasterTechClases

use batch_1;


drop table Customer_tbl
drop table [order]
create table Customer_tbl(
C_id int primary key,
C_Name varchar(50),
C_Address varchar(max),
City varchar(50) );


select * from Customer_tbl
insert into Customer_tbl values(0,'Unknown','Unknown address','No city');


insert into Customer_tbl values(1,'Ali','home no :8','Mohali');
insert into Customer_tbl values(2,'Aman','home no :5','Mohali');
insert into Customer_tbl values(3,'Arusi','home no :6','Mohali');
insert into Customer_tbl values(4,'Aehmad','home no :2','Mohali');


create table [order]
(
Ord_Id int primary key,
Item varchar(50),
Quantity int,
Price_Of_1 int,
C_id int foreign key references Customer_tbl(C_id)
on delete set default
on update set default
);


insert into [order] values (111,'Hardisk',2,500,3);
insert into [order] values (222,'Ram',1,300,1);
insert into [order] values (333,'Keyboard',3,400,4);
insert into [order] values (444,'Mouse',2,300,2);
insert into [order] values (555,'Speaker',1,5000,2);


insert into [order] values (662,'USB',2,1000,5);


select * from Customer_tbl;
select * from [order];


delete from Customer_tbl where C_id =3;
delete from [order] where C_id=5;


alter table [order] drop constraint FK__order__C_id__60A75C0F;


alter table [order] ADD foreign key (C_id) references Customer_tbl(C_id);


update customer_tbl set c_id=7 where c_id=2;
--cascading Referential Integrity constraints
-- no action - mean deafault action
--casacade -parent delete then child also delete 1. on delete cascade , on update cascade
-- set null -if primary key data remove then foreign key data set null & foreign key not null set at creation time
-- 1. on delete set null , on update set null


--set default -- default constraint set
--1. on delete set default,on update set default


alter table [order] add default 0 for c_id