| ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated [message #638092] |
Tue, 02 June 2015 03:55  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
Please let me know, even if I am using "ENABLE NOVALIDATE" on script of primary key creation, below error is occurring.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 2 14:11:18 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter table tagicrm.CNFGTR_RISK_GRID_DETAIL_TAB
2 add constraint PK_RISK_GRID primary key (NUM_REFERENCE_NUMBER,DAT_REFERENCE_DATE, SEQ_NO, BLK_ID)
3 using index
4 tablespace INS
5 enable novalidate;
add constraint PK_RISK_GRID primary key (NUM_REFERENCE_NUMBER,DAT_REFERENCE_DATE, SEQ_NO, BLK_ID)
*
ERROR at line 2:
ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated
Please suggest what can be done to implement the primary key on it, without deleting the records.
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
|
|
| Re: ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated [message #638096 is a reply to message #638095] |
Tue, 02 June 2015 04:11  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 02 June 2015 10:03
Note that lying Oracle saying you have a PK when you have is not a good idea.
Oracle uses this information to build execution plan and you may have some performances issues and maybe even wrong result.
I think, not tested, that it'll behave well enough because it's not been created as RELY. Rely...well, all bets are off.
|
|
|
|
|
|
|
|
| Re: ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated [message #638113 is a reply to message #638109] |
Tue, 02 June 2015 08:13 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
True solution would be taking care of duplicate keys and then creating PK with VALIDATE. If NOVALIDATE is justified, then you must follow pablolee's suggestion:
1.Create non-unique index on NUM_REFERENCE_NUMBER,DAT_REFERENCE_DATE, SEQ_NO, BLK_ID
2. Create PK with NOVALIDATE
But keep in mind, you are trading unique for a non-unique index, so there might be some performance cost.
SY.
|
|
|
|
|
|
| Re: ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated [message #638117 is a reply to message #638114] |
Tue, 02 June 2015 14:36 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
THe only difference I am aware of is that there is always one extra block read when using a predicate on a non-unique index. Even if you define a unique constraint, Oracle always checks the next leaf block of the index as well as the block containing the key you want, presumably to be sure that there isn't another matching key. I've tested this a few times, on releases up to 12.1.0.2
In general, I always use non-unique indexes, so that if necessary I can disable the constraint without dropping the index.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
