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

User · Accepted Answer

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  lt  lt sequence name gt  gt  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

User · Answer

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

User · Answer

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

[oracle] Unique constraint violation during insert: why? (Oracle)

Examples related to oracle

Examples related to unique-constraint