| Sequence giving me ora-00001 ? (unique constraint violated) [message #394825] |
Mon, 30 March 2009 07:48  |
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
|
Member |
|
|
I have Googled this, and so far only come up with 1 answer. Will give my thoughts at the end.
This is difficult for me to explain, but I will try my best.
We have tables, each table with a "before insert" trigger.
The purpose of the before insert trigger is to assign a primary key to the table (called the ID field), by calling a sequence (each table has its own trigger and its own sequence).
Summary of transaction:
1. insert into table A values (0,.......), where 0 is the place-holder for the ID column.
2. The before insert trigger executes. If a value of 0 is supplied for the ID, the trigger takes over, and reassigns a new ID by calling a sequence. A new value is given to :new.ID_column
3. The transaction continues and the new ID is now inserted into the table.
The users are complaining that they are getting an ora-00001 error (unique constraint violated on the ID column).
I can't see this happening, because
1. a sequence is ALWAYS unique, so each user must get his own unique value back from Oracle.
2. I looked at the program and they always use a 0 (so the ID is never any other value), so the trigger always picks this up and assigns the next value in the sequence.
Questions:
1. Could this be a bug in Oracle ?
2. Could this be a problem with RAC (we have 2 nodes in this case serving 1 application server).
I fixed this temporarily by increasing the sequence max value, but I don't think the problem is on the database.
The developers keep insisting this is a database problem, and I don't think so. The problem is very hard to duplicate, and we've only had it 4 times this year.
Any ideas ?
Dirk
|
|
|
|
|
|
| Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #394884 is a reply to message #394825] |
Mon, 30 March 2009 13:04  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
This sounds like something very difficult to track down. and I say this is a nice way, really, but that is an asinine way of assigning a unique value.
Why not just use the sequence.nextval in the INSERT itself. you are prone to so many errors while trying to reinvent the way a unique ID should be created/inserted.
|
|
|
|
| Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #394896 is a reply to message #394825] |
Mon, 30 March 2009 13:43 |
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
|
Member |
|
|
In reply to Joy_Division:
Thank you very much. I agree, and will definately look into this. Thank you for the suggestion.
In reply to GentleBabu:
The reason I increase the max value -
The sequence is used to generate the primary key for the table. For some reason the primary key overtook the nextval of the sequence - the lower value of the sequence meant that new primary key values were being generated which already existed in the table.
I had to increase the max value of the sequence to be higher than the max(ID) ... the primary key of the table.
My difficulty is in finding out why the values in the table overtook the next value of the sequence.
In summary: I could ask the developers to build in some error handling in the application, so that the values are dumped somewhere the next time this error occurs.
As joy_division mentioned this is a difficult one to troubleshoot. The problem also keeps moving, and has not occured twice on the same table / sequence ...
Dirk
|
|
|
|
|
|
| Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #395941 is a reply to message #394825] |
Fri, 03 April 2009 04:15 |
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
|
Member |
|
|
Hi everyone, I managed to solve this one. The developers insisted that this was a database problem.
Fortunately I had insert triggers on some of these tables, which kept track of what they were inserting. The rule on our database is to insert the value 0 in the place of the primary key, and a trigger with sequence will then assign the next primary key.
BUT, one of them broke this rule, and inserted actual primary key values in one of the applications, which then eventually caused the key and the sequence to clash with each other (the application tried to assign a duplicate primary key).
So in short, with the help of triggers and auditing their inserts, I was able to prove that the problem was in the application, and not in the database.
Regards
Dirk
|
|
|
|
|
|
| Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #395995 is a reply to message #395941] |
Fri, 03 April 2009 08:08 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| dirkm wrote on Fri, 03 April 2009 05:15 |
So in short, with the help of triggers and auditing their inserts, I was able to prove that the problem was in the application, and not in the database.
|
Glad to hear it...BUT...what is to prevent someone from doing it again? You really really need to change this poor design. It may have sounded like a "thinking outside of the box" thing to do at design time, but the person did not think ahead. This is the purpose of an Oracle sequence; so there cannot possibly be a duplicate value.
You did mention that you happened 4 times this year. That's about a 4.5% chance of this occurring, or every 22 days. I can tell you that this is going to happen again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
