ORA-02437: cannot validate - primary key violated
====================================================
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;
Table dropped.
SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;
Table created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;
ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084074) - primary key violated
SQL>
SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) DISABLE ;
Table altered.
SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084075) - primary key violated
SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084076) - primary key violated
======================
CAUSE & sOLUTION:
======================
This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.
Solution
=============
------- First, create a NON-UNIQUE index on the same fields that you want to
--------include in the constraint.
SQL>
SQL>
SQL> alter table HALIM_TEST drop constraint SYS_C0084076 ;
Table altered.
SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);
Index created.
-------- Then add the unique or primary key constraint.
SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;
Table altered.
oR
SQL>
SQL>
SQL> ALTER TABLE HALIM_TEST ENABLE NOVALIDATE PRIMARY KEY;
Table altered.
SQL>
SQL>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment