Unique constraint violation during insert: why? (Oracle)

Question

I'm trying to create a new row in a table. There are two constraints on the table -- one is on the key field (DB_ID), the other constrains a value to be one of several the the field ENV. When I do an insert, I do not include the key field as one of the fields I'm trying to insert, yet I'm getting this error:

unique constraint (N390.PK_DB_ID) violated

Here's the SQL that causes the error:

insert into cmdb_db 
   (narrative_name, db_name, db_type, schema, node, env, server_id, state, path) 
values 
   ('Test Database', 'DB', 'TYPE', 'SCH', '', 'SB01', 381, 'TEST', '')

The only thing I've been able to turn up is the possibility that Oracle might be trying to assign an already in-use DB_ID if rows were inserted manually. The data in this database was somehow restored/moved from a production database, but I don't have the details as to how that was done.

Any thoughts?

Answer 1

Presumably, since you're not providing a value for the DB_ID column, that value is being populated by a row-level before insert trigger defined on the table. That trigger, presumably, is selecting the value from a sequence.

Since the data was moved (presumably recently) from the production database, my wager would be that when the data was copied, the sequence was not modified as well. I would guess that the sequence is generating values that are much lower than the largest DB_ID that is currently in the table leading to the error.

You could confirm this suspicion by looking at the trigger to determine which sequence is being used and doing a

SELECT <<sequence name>>.nextval
  FROM dual

and comparing that to

SELECT MAX(db_id)
  FROM cmdb_db

If, as I suspect, the sequence is generating values that already exist in the database, you could increment the sequence until it was generating unused values or you could alter it to set the INCREMENT to something very large, get the nextval once, and set the INCREMENT back to 1.

Answer 2

Your error looks like you are duplicating an already existing Primary Key in your DB. You should modify your sql code to implement its own primary key by using something like the IDENTITY keyword.

CREATE TABLE [DB] (
    [DBId] bigint NOT NULL IDENTITY,
    ...

    CONSTRAINT [DB_PK] PRIMARY KEY ([DB] ASC),

); 

Answer 3

It looks like you are not providing a value for the primary key field DB_ID. If that is a primary key, you must provide a unique value for that column. The only way not to provide it would be to create a database trigger that, on insert, would provide a value, most likely derived from a sequence.

If this is a restoration from another database and there is a sequence on this new instance, it might be trying to reuse a value. If the old data had unique keys from 1 - 1000 and your current sequence is at 500, it would be generating values that already exist. If a sequence does exist for this table and it is trying to use it, you would need to reconcile the values in your table with the current value of the sequence.

You can use SEQUENCE_NAME.CURRVAL to see the current value of the sequence (if it exists of course)