Identifying rows causing the Data Integrity problems

Lets assume a scenario, where one has to disable the constraints, for loading the data asynchronously in different tables.

Once the data is loaded, constraints needs to be re-enabled

Now at the time of enabling the constraints, if we get the “parent keys not found” error, how to find the rows causing this problem?

Let’s see a small example using the SCOTT/TIGER schema

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> alter table emp disable constraint fk_deptno;

Now insert a row, with deptno not present in dept table

SQL> insert into emp (empno,ename,deptno) values (9999,’test’,50);

1 row created.

SQL> commit;

Execute a script to store the exception rows, after enabling the constraint

SQL> @?/rdbms/admin/utlexcpt.sql

Table created.

SQL> desc exceptions
Name Null? Type
—————————————– ——– —————————-
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

Now inseted of using alter table emp enable constraint fk_deptno; use

SQL> alter table emp enable validate constraint fk_deptno exceptions into exceptions;

This will add the details of the rows causing the integrity problems in the exceptions table.

SQL> select * from exceptions;

ROW_ID OWNER TABLE_NAME CONSTRAINT
—————— ————————– ———————— ———-
AAAMgHAAEAAAAAfAAC SCOTT EMP FK_DEPTNO

We can now identify the row from EMP table causing the problem

SQL> select e.* from emp e, exceptions ex where e.rowid = ex.row_id and ex.table_name = ‘EMP’
and ex.constraint = ‘FK_DEPTNO’
/

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- —–
9999 TEST 50

Advertisements
This entry was posted in Oracle Data Integrity problems and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s