Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00001: unique constraint Violation (Oracle 9i)
ORA-00001: unique constraint Violation [message #275967] Tue, 23 October 2007 09:16 Go to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Friends,

when i try to insert a row into a table the following error is thrown:

" ORA-00001: unique constraint (TESTADMIN.SYS_C001686) violated ".

The rows inserted into the destination table is being fetched from another table and the row is inserted into the
destination table.

I have checked the destination table for the duplicate values (checked with column assocaited with Index - SYS_C001686),
and 100% sure that there is no such unique key earlier to this current value,but still it throws this error.

I have a java screen and on a button click, it tries to insert the row in the destination table
and when it does it throws such error.

But when i tried to insert the same row using a direct insert statement in the DB, it got inserted without
any issues.


Can someone please help me on this strange behaviour and suggest anyway to overcome this ?
Re: ORA-00001: unique constraint Violation [message #275971 is a reply to message #275967] Tue, 23 October 2007 09:22 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Obviously it's not the same exact data.

I would say there is something wrong with how your Java program is passing the data.
Re: ORA-00001: unique constraint Violation [message #275972 is a reply to message #275971] Tue, 23 October 2007 09:34 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member

Thanks for your quick reply.

But the problem is that it works fine without any issues 90% of the time. I get this error randomnly and if i
try the same after a day or so, it does work fine !!!!

So will it still be the coz of the parameters being passed by JAVA program or any issues with a corrupt INDEX..

Seek ur suggestions plz..
Re: ORA-00001: unique constraint Violation [message #275976 is a reply to message #275967] Tue, 23 October 2007 09:43 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What about concurrency ?
Could different users be trying to add the same data?
Re: ORA-00001: unique constraint Violation [message #275978 is a reply to message #275976] Tue, 23 October 2007 09:45 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
No concurrent access as of now .. Single user trying at a time
Re: ORA-00001: unique constraint Violation [message #276003 is a reply to message #275978] Tue, 23 October 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about implicit conversions?

Regards
Michel
Re: ORA-00001: unique constraint Violation [message #276042 is a reply to message #276003] Tue, 23 October 2007 14:03 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Hi Michael,

There is no implicit conversion that takes place jus before inserting the data into the table. But i do have a decode function that decodes a value for one of the column value in the row that will be inserted. Will that cause any problem leading to ORA-00001: unique constraint Violation error ?
Re: ORA-00001: unique constraint Violation [message #276046 is a reply to message #275967] Tue, 23 October 2007 14:21 Go to previous messageGo to next message
Kevin Meade
Messages: 1924
Registered: December 1999
Location: Connecticut USA
Senior Member
I suggest, you drop the unique constraint (this will drop the unique index at the same time), load the table with a dataset known to cause the error, and then find the duplicate row. This will prove to you that infact your are attempting to load duplicates.

Alternatively, you could trap the error in your code and write the duplicate row out to an errors table.

If you are getting this error, then you are loading duplicate keys.

Good luck, Kevin
Re: ORA-00001: unique constraint Violation [message #276047 is a reply to message #276042] Tue, 23 October 2007 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Kevin said you can use an insert select with a log error clause.

You can also post your code maybe we can find something.

Regards
Michel
Re: ORA-00001: unique constraint Violation [message #276049 is a reply to message #275967] Tue, 23 October 2007 14:45 Go to previous messageGo to next message
Kevin Meade
Messages: 1924
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Michel

balamurugan.murthy please read up on using the LOG ERRORS clause before you try it; it has restrictions and limitations that you need to know about before employing its services. Otherwise you will get frustrated when first using it. Here is one link to the topic:

http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php

Good luck, Kevin
Re: ORA-00001: unique constraint Violation [message #276057 is a reply to message #276049] Tue, 23 October 2007 16:02 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Kevin, Michel,

Thanks a lot for your quick replies.

I will try as per ur suggestion and get back to you with some positive results.

Regards,
Bala.
Re: ORA-00001: unique constraint Violation [message #276058 is a reply to message #276057] Tue, 23 October 2007 16:03 Go to previous messageGo to next message
Kevin Meade
Messages: 1924
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks, Kevin
Re: ORA-00001: unique constraint Violation [message #276061 is a reply to message #275967] Tue, 23 October 2007 16:06 Go to previous messageGo to next message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
But Kevin,

In reply to your statment:

"I suggest, you drop the unique constraint (this will drop the unique index at the same time), load the table with a dataset known to cause the error, and then find the duplicate row. This will prove to you that infact your are attempting to load duplicates."

--- Actually as per my previous update, i have tried inserting this record by issuing an insert query directly against the table and it gets inserted without any issues. The error is thrown only if i try to insert on a button click present in the front end java screen. On button click it fetches data from selected vlues on the screen and tries to insert into table and gets this error message thrown..

anyway i will try as per ur suggestion and get back to you.

Thanks,
Bala.
Re: ORA-00001: unique constraint Violation [message #276123 is a reply to message #276061] Wed, 24 October 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The error is thrown only if i try to insert on a button click present in the front end java screen. On button click it fetches data from selected vlues on the screen and tries to insert into table and gets this error message thrown..

If it tries to insert a row that it previously fetched it is normal it gets a unique constraint violation. It should update instead. Or I miss something?

Regards
Michel
Re: ORA-00001: unique constraint Violation [message #276249 is a reply to message #275967] Wed, 24 October 2007 08:57 Go to previous messageGo to next message
Bill B
Messages: 1066
Registered: December 2004
Senior Member
Your statements are telling you where the error is. Oracle will NOT throw a ORA-00001 unless you are attempting to insert a duplicate row. You say that when you do the insert manually it always works, so there is only one possibility. The problem is with your application. Is it possible that people are double clicking the button and firing the insert logic twice? To combat this, clear the values after they are inserted.
Re: ORA-00001: unique constraint Violation [message #276334 is a reply to message #276249] Wed, 24 October 2007 21:51 Go to previous messageGo to next message
rleishman
Messages: 3684
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have you checked in DBA_CON_COLUMNS to make sure that SYS_C001686 is the unique/PK constraint that you think it is. Or are you just assuming?

For example, it could be another unique index on the same table, or it could be on another table that you are populating with a TRIGGER on the first table.

Ross Leishman
Re: ORA-00001: unique constraint Violation [message #276436 is a reply to message #276334] Thu, 25 October 2007 02:55 Go to previous message
balamurugan.murthy
Messages: 21
Registered: October 2007
Junior Member
Yes Leishman, I have checked with the unique/pk constraint too, it is correct as i expected. Am iam trying to capture the error, as kevin said using error logs.

Thank you all for ur valuable suggestions....
Previous Topic: oracle help
Next Topic: avoiding null results
Goto Forum:
  


Current Time: Wed Jul 23 01:04:32 CDT 2014

Total time taken to generate the page: 0.11627 seconds