ORA-02437: cannot validate (table_name) primary key violated

ORA-02437: cannot validate (table_name) primary key violated

Error
I want to create an primary key on one of my existing table which having data in it.
When i tried to create then i got the following error:


SQL> ALTER TABLE hr.test ADD CONSTRAINT test_pk primary key (id);
ALTER TABLE hr.test ADD CONSTRAINT test_pk primary key (id)
*
ERROR at line 1:
ORA-02437: cannot validate (HR.TEST_PK) - primary key violated

Cause
The table detail as show below. It has duplicate records in it. Thats why it violated or throw error.

SQL> select * from hr.test;

ID T_DATE
---------- ---------
1 10-OCT-18
2 10-OCT-18
3 10-JAN-18
3 10-JAN-18
5 10-JAN-19
5 10-JAN-19
5 10-JAN-19
5 10-JAN-19
4 08-JAN-19

9 rows selected.

Solution
Solution for creating primary key on that table is to remove the duplicated record present in the table.

1. Create the exceptional table

-- with script
@?/rdbms/admin/utlexcpt.sql

or
-- otherwise create table directly -
create table exceptions
(
row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30)
);

Note: If table is not present then you got the error: ORA-02445: Exceptions table not found
Create table in user schema like create table hr.exceptions or connect direct with user and create table.

2. Run the following command so it move all the duplicate record with ROW_ID to exceptions table.

alter table hr.test add constraint test_pk primary key(id) exceptions into exceptions;

SQL> alter table hr.test add constraint test_pk primary key(id) exceptions into exceptions;
alter table hr.test add constraint test_pk primary key(id) exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (HR.TEST_PK) - primary key violated

3. Check the duplicate rows with exceptions table present in HR schema.

select rowid, id from hr.test where rowid in
(select row_id from hr.exceptions where table_name='TEST');

ROWID ID
------------------ ----------
AAAFVVAAEAAABzNAAD 3
AAAFVVAAEAAABzNAAC 3
AAAFVVAAEAAABzNAAH 5
AAAFVVAAEAAABzNAAG 5
AAAFVVAAEAAABzNAAF 5
AAAFVVAAEAAABzNAAE 5

4. Now you have duplicate rows with unique rowid.
In data ID column value 3 has 2 rows and 5 has 4 rows. So you have to delete 1 row from ID value 3 and 3 rows from ID value 5.

-- Remove manually by using it.
delete from hr.test where rowid='AAAFVVAAEAAABzNAAD';

--together with help of excel or in clause
delete from hr.test where rowid in ('AAAFVVAAEAAABzNAAG','AAAFVVAAEAAABzNAAF','AAAFVVAAEAAABzNAAE');
commit;

5. After delete create primary key as follows:

SQL> alter table hr.test add constraint test_pk primary key(id) ;
Table altered.

6. Check primary key present on table with following query:

col table_name for a10
col column_name for a15
col owner for a10
select cols.table_name,cols.column_name,cols.position,cons.status,cons.owner
from dba_constraints cons,dba_cons_columns cols
where cons.table_name = 'TEST'
and cons.owner='HR'
and cons.constraint_type = 'P'
and cons.constraint_name = cols.constraint_name
and cons.owner = cols.owner
order by cols.table_name,cols.position;

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.