插入期间违反唯一约束:为什么? (甲骨文) [英] Unique constraint violation during insert: why? (Oracle)

问题描述

我正在尝试在表中创建新行.表上有两个约束-一个约束在键字段(DB_ID)上，另一个约束将值限制为ENV字段中的几个.当我执行插入操作时，我没有将键字段作为要插入的字段之一，但是却出现此错误:

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

以下是导致错误的SQL:

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', '')

我唯一能解决的问题是，如果手动插入行，Oracle可能会尝试分配一个已经在使用的DB_ID.该数据库中的数据是以某种方式从生产数据库中恢复/移动的，但是我没有有关如何完成操作的详细信息.

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.

有什么想法吗?

推荐答案

大概是因为您没有为DB_ID列提供值，所以该值将由行级填充，然后才在桌子.大概是那个触发器正在从序列中选择值.

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.

由于数据是从生产数据库中移出的(大概是最近的)，所以我的下注是复制数据时，序列也不会被修改.我猜想该序列所生成的值比导致错误的表中当前最大的DB_ID低得多.

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

并将其与

SELECT MAX(db_id)
  FROM cmdb_db

如果我怀疑序列正在生成数据库中已经存在的值，则可以递增序列直到生成未使用的值，或者可以更改序列以将INCREMENT设置为非常大的值，得到nextval一次，然后将INCREMENT设置回1.

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.

这篇关于插入期间违反唯一约束:为什么? (甲骨文)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！