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>
We did {insert item} and it failed!
1 day ago
No comments:
Post a Comment