Home » RDBMS Server » Server Administration » Sequence giving me ora-00001 ? (unique constraint violated) (10g, sles 9 (RAC))
Sequence giving me ora-00001 ? (unique constraint violated) [message #394825] Mon, 30 March 2009 07:48 Go to next message
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 #394827 is a reply to message #394825] Mon, 30 March 2009 08:07 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

>>ora-00001 error (unique constraint violated on the ID column).

Check any duplicate entry is there or not.

Check your current value from your sequence.

>> fixed this temporarily by increasing the sequence max value, but I don't think the problem is on the database.

It's related your database problem. Why you need to increase max value??

Babu
Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #394884 is a reply to message #394825] Mon, 30 March 2009 13:04 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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 #395143 is a reply to message #394896] Tue, 31 March 2009 08:18 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You really need to just user sequence.nexval. You cannot possibly get dupes even in a RAC environment. You may not get numbers in order is sorting by timestamp of the row, but what you really want is a primary key, so order of sequence is irrelevant.
Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #395941 is a reply to message #394825] Fri, 03 April 2009 04:15 Go to previous messageGo to next message
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 #395952 is a reply to message #395941] Fri, 03 April 2009 05:15 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
the application tried to assign a duplicate primary key).


Thanks for the feedback.

Babu
Re: Sequence giving me ora-00001 ? (unique constraint violated) [message #395995 is a reply to message #395941] Fri, 03 April 2009 08:08 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: Drop tablespace in Oracle 8i
Next Topic: Not able to get data in ascending in Oracle10g
Goto Forum:
  


Current Time: Mon Dec 05 19:01:45 CST 2016

Total time taken to generate the page: 0.16238 seconds