Constraints: How to resolve the duplicated primary key exceptions

After disabling the constraints, One can enter invalid values into the tables as per the constraint condition. for example, if you disable primary key constraint, it is possible to enter the duplicate value in primary key column and commit the same data in table. till this you will not have the problem.

Problem will arise if you try to enable the primary key. In presence of duplicate values. If your table is small it easy to judge what rows have invalid values but this problem will become a junk when your table is big, say millions of row. ...........

but not to worry oracle database has a process to identify those row which have some invalid data as per the constraints. while enabling the constraint, we can detect all rows which may have some invalid data. Following is one demo on small sample table:

Let us say, we have a table CON with primary key in ID column.

SQL> conn hr/hr
Connected.
SQL> desc con
Name Null? Type
----------------------------------------- -------- -----------------------

ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(20)
CITY VARCHAR2(10)
SALARY NUMBER(10,2)

SQL> select * from con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
10 ABC B 1000
11 XYZ C 2000
13 DIFF
100 FFF 2000
12 SOME 2000


Let us disable the primary key, assuming constraint name of primary key in ID column is CON_PR.

SQL> alter table con disable constraint con_pr;

Table altered.

SQL> select * from con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
10 ABC B 1000
11 XYZ C 2000
12 FFF 2000
13 DIFF

SQL> insert into con values(12,'SOME',NULL,2000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
10 ABC B 1000
11 XYZ C 2000
12 FFF 2000
13 DIFF
12 SOME 2000

SQL> alter table con enable constraint con_pr;
alter table con enable constraint con_pr
*
ERROR at line 1:
ORA-02437: cannot validate (HR.CON_PR) - primary key violated


To detect all row which are violating the primary key constraint, we need to have an output table EXCEPTIONS. In this table we can generate ROWID of all the rows by which we are getting the exception whenever we enabling it.


SQL> desc exceptions
ERROR:
ORA-04043: object exceptions does not exist

So if this table is not in current schema, we can create it by using a sample script stored in ORACLE_HOME.

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)


Before we start every time the exception we should check, EXCEPTION table should not have any row. We can avoid ambiguity if we are doing same things on multiple table. Advice is to use once with one table.

SQL> select * from exceptions;

no rows selected


To generate the ROWID into output table, while enabling constraint we need to specify clause exceptions into exceptions:


SQL> alter table con enable constraint con_pr exceptions into exceptions;
alter table con enable constraint con_pr exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (HR.CON_PR) - primary key violated


Here we got error. means......some rows are violating the constraints. Let us check output table for those potential set of ROWIDs which are violating the constraints. we can examine ROW_ID, CONSTRAINT columns to verify it.

SQL> select * from exceptions;

ROW_ID OWNER TABLE_NAME
------------------ ------------------------------ ------------------------------

CONSTRAINT
------------------------------
AAARbJAAEAAAAG0AAE HR CON
CON_PR

AAARbJAAEAAAAG0AAC HR CON
CON_PR


SQL> select rowid,id,name from con;

ROWID ID NAME
------------------ ---------- --------------------
AAARbJAAEAAAAG0AAA 10 ABC
AAARbJAAEAAAAG0AAB 11 XYZ
AAARbJAAEAAAAG0AAC 12 FFF
AAARbJAAEAAAAG0AAD 13 DIFF
AAARbJAAEAAAAG0AAE 12 SOME


Now move these row to some other table.

SQL> create table copy_con as select * from con where rowid in (select ROW_ID fr
om exceptions);

Table created.

SQL> select * from copy_con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
12 FFF 2000
12 SOME 2000

For the time-being delete these rows from actual table and enable the constraint.

SQL> delete con where rowid in (select ROW_ID from exceptions);

2 rows deleted.

SQL> commit;

Commit complete.


Before once again exception starts truncate the output table.

SQL> truncate table exceptions;

Table truncated.

SQL> alter table con enable constraint con_pr exceptions into exceptions;

Table altered.

there is no exeception. we can cross-verify from output table name.

SQL> select * from exceptions;

no rows selected


If it is matter of few row, in my case i am correcting the row to have unique values in ID columns and then move these rows to actual table.


SQL> update copy_con set id=100 where name='FFF';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from copy_con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
100 FFF 2000
12 SOME 2000

SQL> insert into con select * from copy_con;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from con;

ID NAME CITY SALARY
---------- -------------------- ---------- ----------
10 ABC B 1000
11 XYZ C 2000
13 DIFF
100 FFF 2000
12 SOME 2000

Enjoy.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

What I learned about Oracle

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today