SQL on delete cascade and on delete set null Constraint- Tutorial using Oracle

Create the student table

 
SQL> create table student(id number, name varchar2(10), address varchar2(10), gender varchar2(10), sid number);

Output
Table created.

Insert records into the student table

 
  SQL> insert into student values(421,'satish','vellore','male',111);
  1 row created.
  
  SQL> insert into student values(422,'ram','mumbai','male',111);
  1 row created.
  
  SQL> insert into student values(423,'jeff','pune','male',111);
  1 row created.
  
  SQL> insert into student values(424,'chris','us','male',111);
  1 row created.
  
  SQL> insert into student values(425,'harini','uk','female',112);
  1 row created.

View the records from the student table

 
SQL> select * from student;

Output
        ID NAME       ADDRESS    GENDER            SID
---------- ---------- ---------- ---------- ----------
       421 satish     vellore    male              111
       422 ram        mumbai     male              111
       423 jeff       pune       male              111
       424 chris      us         male              111
       425 harini     uk         female            112

Create the school table

 
SQL> create table school(sid number, sname varchar2(10), slocation varchar2(10));

Output
Table created.

Insert records in the school table

 
SQL> insert into school values (111,'scope','sjt');
1 row created.
  
SQL> insert into school values (112,'sense','tt');
1 row created.

View the school table

 
SQL> select * from school;

Output
       SID SNAME      SLOCATION
---------- ---------- ----------
       111 scope      sjt
       112 sense      tt

Establishing foreign key using on delete cascade clause

 
SQL> alter table student add constraint student_sid_fr foreign key(sid) references
  school(sid) on delete cascade;

Output
Table altered.

Establishing foreign key using on delete set null

 
SQL> alter table student add constraint student_sid_fr foreign key(sid) references
   school(sid) on delete set null;
  

Output
Table altered.