ORA-02292: integrity constraint (TEST.SYS_C008202) violated

Following example show the error and its various fixes which we can handle in Oracle:

Error

-- Create Primary table
SQL> create table testprimary(id number primary key, name varchar2(10));
Table created.
-- Create Foregin table
SQL> create table testforeign(f_id number, f_name varchar2(10), P_id number, foreign key(p_id) references testprimary(id) );
Table created.

-- Generated Error:
SQL> delete from testprimary where id = 1;
delete from testprimary where id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.SYS_C008202) violated - child record
found


Cause
When we tried to delete data from primary table then we have to first delete from its reference table (child table)
While creating foreign key you can specify the clause ON DELETE clause to specify reason when the rows in the parent table are deleted.
ON DELETE CASCADE: if a row in the parent is deleted, then all the rows in the child table that reference to that row will be deleted.
ON DELETE SET NULL: if a row in the parent is deleted, then all the rows in the child table reference to that row will be set to NULL for the foreign key columns.

Solution

Option 1: Fixed the Data in table by finding constraint information

1. Find the Foreign key table name and column name from constraint name got in error:

col table_name for a15
col owner for a15
col column_name for a15
col primaryowner for a10
col primaryconstraintname for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",a.r_constraint_name "PrimaryConstraintName" from all_constraints a, all_cons_columns b where
A.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_name = 'SYS_C008202';

C OWNER           TABLE_NAME      COLUMN_NAME     PrimaryOwn PrimaryConstrai
- --------------- --------------- --------------- ---------- ---------------
R TEST            TESTFOREIGN     P_ID            TEST       SYS_C008201

2. Now found the primary key of the Primary table.
Note: use upper query output constraint name and fetch primary key details.

col table_name for a15
col owner for a15
col column_name for a15
col primaryowner for a10
col primaryconstraintname for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",a.r_constraint_name "PrimaryConstraintName" from all_constraints a, all_cons_columns b where
A.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_name = 'SYS_C008201';

C OWNER           TABLE_NAME      COLUMN_NAME     PrimaryOwn PrimaryConstrai
- --------------- --------------- --------------- ---------- ---------------
P TEST            TESTPRIMARY     ID

3. Now you can fetch the numbers of rows present in Child table which you want to delete in Primary table with IN Clause query.

-- I m just giving example you can clear your data also with it.
select * from TESTFOREIGN where p_id in ( select id from testprimary where id = 1;)


Option 2: You can delete data with disable or enable constraints and performed operation
Note: It mostly worked in case that you want to completly truncate the data from the tables.

-- Disable
ALTER TABLE child_table DISABLE CONSTRAINT fk_name;

--Enable
ALTER TABLE child_table DISABLE CONSTRAINT fk_name;


Option 3: Drop the foreign key constraint:
Note: It worked if you donot need the key between tables.

ALTER TABLE child_table DROP CONSTRAINT fk_name;