Foreign Keys with ON DELETE CASCADE option allows us to create parent child tables which automatically deletes referenced data in child tables when deleted from parent table.
Normally when we try to delete a row from a parent table which is having related record in a child table,oracle will not allow to delete the row from parent table.Oracle will throw ORA-02292: integrity constraint violated – child record found error.

Here we need to remove the record from the child table first in order to remove the parent record.
To avoid this difficulty we can add ‘ON DELETE CASCADE‘ statement during the creation of Child table foreign keys.With cascade delete option, if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.

Let us create an EMPLOYEE table which holds the employee information like name ,id and salary.
Here emp_id is the primary key column.

SQL> create table EMPLOYEE(
name VARCHAR2(50),
emp_id NUMBER,
salary NUMBER(8,2),
CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (emp_id));
Table created

Now create an Employee address table which holds the address details of the employees.The address table is linked to EMPLOYEE table through the foreign key emp_id. Here we are not adding ON DELETE CASCADE option in order to explain the ORA-02292: integrity constraint violated error.

SQL> create table EMPLOYEE_ADDRESS(
address varchar2(200),
emp_id number,
CONSTRAINT EMP_ADD_FK FOREIGN KEY (emp_id)REFERENCES EMPLOYEE(emp_id));

Table created

Now let us insert one record in EMPLOYEE table.

SQL> insert into EMPLOYEE values('
'ABCD',1,12345);
1 row inserted

Now let us add address for this particular employee in EMPLOYEE_ADDRESS table.

SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',1);
1 row inserted

If we delete a row from the EMPLOYEE table and if a reference exists in EMPLOYEE_ADDRESS table oracle will throw an error showing reference integrity dependency.

SQL> delete from EMPLOYEE where emp_id =1;
ORA-02292: integrity constraint (TEST.EMP_ADD_FK) violated - child record found

Now let us drop the table EMPLOYEE_ADDRESS and recreate it with foreign key and with ON DELETE CASCADE option.

SQL> drop table EMPLOYEE_ADDRESS;
Table dropped.
SQL> create table EMPLOYEE_ADDRESS(
address varchar2(200),
emp_id number,
CONSTRAINT EMP_ADD_FK
 FOREIGN KEY (emp_id)  REFERENCES EMPLOYEE(emp_id) ON DELETE CASCADE);

Table created
SQL> insert into EMPLOYEE_ADDRESS values('Address abcd efgh',1);
1 row inserted

SQL> select count(*) from employee;
COUNT(*)
1

SQL> select count(*) from employee_address;
COUNT(*)
1

Now directly delete the record from employee table.

SQL> delete from EMPLOYEE where emp_id =1;
1 row deleted

SQL> select count(*) from employee;
COUNT(*)
0

SQL> select count(*) from employee_address;
COUNT(*)
0

This deletes the related child record from EMPLOYEE_ADDRESS table automatically.

Related Articles,ORA-02449: unique/primary keys in table referenced by foreign
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key
ALTER TABLE to ADD PRIMARY KEY in Oracle.

Technorati Tags:
Foreign Keys, ON DELETE CASCADE