| Use of ON DELETE CASCADE , ON DELETE SET NULL & TRUNCATE TABLE CASCADE in Oracle |

Please subscribe my channel and don't forget to hit the bell icon to get latest videos
In this Video we will understand the Use of ON DELETE CASCADE , ON DELETE SET NULL & TRUNCATE TABLE CASCADE in Oracle
Scripts Are Given below
----------------------------------------
Use of ON DELETE CASCADE , ON DELETE SET NULL , TRUNCATE TABLE CASCADE
----------------------------------------------------------------------
(1) ON DELETE CASCADE
(2) ON DELETE SET NULL
(3) TRUNCATE TABLE CASCADE (Introduced in 12c)
(1) ON DELETE CASCADE
----------------------------------------------
ON DELETE CASCADE is used to automatically delete all the child records if we delete the parent records
It only with the DELETE command.
If we use TRUNCATE command then it will not work. It will show error message
Example-1
----------
create table parent_Details(p_id number constraint pid_pk primary key,Pname varchar2(100));
create table child_details(c_id number constraint cid_pk primary key ,
cname varchar2(100),
p_id number ,
constraint p_fk foreign key (p_id) references parent_Details(p_id) ON DELETE CASCADE
);
--Inserting Data into Parent_Details
insert into parent_Details (p_id,pname) values (1,'Parent 1');
insert into parent_Details (p_id,pname) values (2,'Parent 2');
--Displaying inserted data
select * from parent_Details;
--Inserting Data into Child_Details
insert into child_details(c_id,cname,p_id) values (101,'Child 1',1);
insert into child_details(c_id,cname,p_id) values (102,'Child 2',1);
insert into child_details(c_id,cname,p_id) values (103,'Child 1',2);
select * from child_details;
TRUNCATE TABLE PARENT_DETAILS;
DELETE FROM PARENT_DETAILS;
select * from child_details;
select * from PARENT_DETAILS;
(2) ON DELETE SET NULL
------------------------------------------
ON DELETE SET NULL is used to automatically set NULL value in the referenced column of child table if we delete the parent record
Example-1
------------------------
DROP TABLE child_details;
DROP TABLE parent_Details;
create table parent_Details(p_id number constraint pid_pk primary key,Pname varchar2(100));
create table child_details(c_id number constraint cid_pk primary key ,
cname varchar2(100),
p_id number ,
constraint p_fk foreign key (p_id) references parent_Details(p_id) ON DELETE SET NULL
);
--Inserting Data into Parent_Details
insert into parent_Details (p_id,pname) values (1,'Parent 1');
insert into parent_Details (p_id,pname) values (2,'Parent 2');
--Displaying inserted data
select * from parent_Details;
--Inserting Data into Child_Details
insert into child_details(c_id,cname,p_id) values (101,'Child 1',1);
insert into child_details(c_id,cname,p_id) values (102,'Child 2',1);
insert into child_details(c_id,cname,p_id) values (103,'Child 1',2);
select * from child_details;
TRUNCATE TABLE PARENT_DETAILS;
DELETE FROM PARENT_DETAILS;
select * from child_details;
select * from PARENT_DETAILS;
(3) TRUNCATE TABLE CASCADE
----------------------------------------------------
It is introduced in oracle 12c .
If we use TRUNCATE COMMAND with parent table , then it will delete all the parent records along with reference child records.
TRUNCATE TABLE Command will work, if you specify ON DELETE CASCADE in the Foreign Key column.
Example-1
------------------
DROP TABLE child_details;
DROP TABLE parent_Details;
create table parent_Details(p_id number constraint pid_pk primary key,Pname varchar2(100));
create table child_details(c_id number constraint cid_pk primary key ,
cname varchar2(100),
p_id number ,
constraint p_fk foreign key (p_id) references parent_Details(p_id) ON DELETE CASCADE
);
--Inserting Data into Parent_Details
insert into parent_Details (p_id,pname) values (1,'Parent 1');
insert into parent_Details (p_id,pname) values (2,'Parent 2');
--Displaying inserted data
select * from parent_Details;
--Inserting Data into Child_Details
insert into child_details(c_id,cname,p_id) values (101,'Child 1',1);
insert into child_details(c_id,cname,p_id) values (102,'Child 2',1);
insert into child_details(c_id,cname,p_id) values (103,'Child 1',2);
select * from child_details;
TRUNCATE TABLE PARENT_DETAILS CASCADE;
select * from child_details;
select * from PARENT_DETAILS;
LIKE THE FACEBOOK PAGE - / oracle-tutorials-10848...
FOLLOW ON Instagram - / nihar8308
FOLLOW ON LinkedIn - www.linkedin.c...
FOLLOW ON TWITTER - / niharra59094760
Email Id: [e-mail]
Phone No- 8018319781
#ON_DELETE_CASCADE
#ON_DELETE_SET_NULL
#Oracle_SQL_PLSQL_Concept

[@sumansauravsatpathy]

Nice way of representing the concept.

[@jitenpanigrahi9260]

I know this concept previously but in this section I have cleared my doubt.
Thanks.

[@NiharSahootutorials]

Thanks Jiten

[@sumansauravsatpathy]

Truncate Table Cascade is the new concept for me..