Gotcha: Oracle Sequences – 1

I just posted about Oracle Sequences here. The very reason I posted was because, I’ve seen several experienced developers seem to fumble on it. We had one at work today.

I recently got a request from our DBA to look at an error message from another group. (I’ve changed the table name etc to employee)

Message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (EMPLOYEE) violated
The error occurred on line 31.

Query String: fuseaction=<program name>l&CFID=&CFTOKEN=<token>

Browser: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36

SQL Error: insert into employee ( employee_id, …) values ( 10000, ….)

The message looked something like above. I immediately noticed the CFID etc. That gave me the clue that it was a Coldfusion program (we still use it). And it tells me, there is a unique constraint violation – meaning you try to insert a value of ID field that already exists in the table.

I’ve seen this before. I took a guess and assumed it was using a Sequence. This kind of error can happen, if Sequence sort of falls behind the actual values for the ID in the table. This is my reply to the DBA:

Here is a little more detail from the Error message you sent me:

  • As you know, the error message is basically saying that the ID value is already there in the table.
  • Index mentioned in the Unique Constraint violation message is EMPLOYEE. This is the primary key and the column in this index is EMPLOYEE_ID.
  • The value mentioned for this field in the INSERT statement is 10000. There must already be a record for this key in the table.
  • Based on a list of sequences I found in the database, I guess, it may be using a Sequence named S_EMPLOYEE_ID.

If there is no other issue with the code and the records, this problem could typically happen when the sequence used for getting the ID value for the column falls out of sync with the actual value in the column. Mixing Sequence and Non-Sequence insert could result in this too. Chances are the program may be using Sequence to insert into the table in some places and some other (possibly Max(ID) + 1) approach in other places.

For e.g., in the development database Victoria gave me, I saw the following:

Max(EMPLOYEE_ID) in the table, EMPLOYEE = 10000
Current Value of the sequence S_EMPLOYEE_ID = 9900

There are rows already inserted in this table with IDs > 900. This means, if someone or program uses the Sequence to insert, it is going to get this error until the sequence is bumped up to the Max(ID) + 1.

Solution (for the DEV example above)

For the above example in DEV,

DBA:

Immediate solution may be to simply bump up the Sequence to match the ID maximum.

ALTER SEQUENCE s_employee_id INCREMENT BY 100;

This will bring the Sequence next value to 10000, which is means NextVal will be than the last ID we saw, so it will not give primary key violation any more.

As you see, ALTER SEQUENCE statement only takes increment. I arrived at the increment by using below formula:

[Max(ID) in the table] Minus (-) [Current value for the Sequence]. For the above example, this is,

10000 – 9900 = 100

To obtain the current Max(ID) from the table, use below query:

SELECT Max(employee_id) FROM employee;

Note: The increment will be different for production.

DEV

A more permanent solution may be to look at the code behind (ColdFusion???) to see if there are any INSERTs into this table that uses Sequence in some place and no sequence approach in another. Otherwise, this can happen again.

Contd…

Advertisements

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s