Cascade delete

In Following Image you can see 2  different result set 

1. result set 1 is before Delete. [Before delete Query]
2. result set 2 is after Delete. [After delete Query]

When you perform Delete operation on dept [parent] table it delete its own table and also delete child table (EMP) table's record which are connected to it.

If we Don't Give  on DELETE CASCADE then it will not allow you to delete this parent table records.

Add new Column with Foreign key Constraint

ADDING NEW COLUMN AFTER TABLE CREATION  with FK Constraint

 alter table emp
 add deptno number(3,0)
 add constraint fk_deptno
 foreign key (deptno) references dept(deptno)
on delete cascade

after above option  table  EMP will be  look like

• Here DEPTNO is Foreign key of DEPT table.

1. ON DELETE CASCADE option is  optionally.  this  option will  delete all the record when user delete any deptno from its parent table DEPT.
2. dept is Parent table.
3. all Child record  will be deleted when its releted Parent table is deleted.