Check Your Subroutines - We are delivering our latest release to internal test today. Had a code review yesterday. Many issues were found. We are fixing the highest priority probl...
The constraint looks right. It appears we are trying to put duplicate keys in this table. But that does not make sense. I use a database sequence to generate the keys. Wait. I check the existing records, and then the sequence, and find the sequence value is off. How did this happen? No clue. The fix should be to move the sequence value up past the exsiting records. But how do you do that?
Well I guess I could drop and recreate the sequence with a high starting value. That does not feel correct. I want to just set the next value. Turns out there is no specific API to do that. Instead I modify the sequence to have a large increment value. Then I select a NEXTVAL once, pushing the next value up to where I want it to be. Finally I reset the increment to 1 on the sequence.
This technique also feels like a lot of work to change the next value. But it appears to be the accepted method to do this. Normally you would not want to be mucking with the sequence value, other than getting the next value.