oracle 插入期间违反唯一约束：为什么？(甲骨文)

Unique constraint violation during insert: why? (Oracle)

提问by Sean

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:

我正在尝试在表中创建一个新行。表上有两个约束——一个是键字段 (DB​​_ID)，另一个约束一个值是几个字段 ENV 之一。当我进行插入时，我没有将关键字段作为我尝试插入的字段之一包含在内，但我收到此错误：

unique constraint (N390.PK_DB_ID) violated

Here's the SQL that causes the error:

这是导致错误的 SQL：

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.

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

Any thoughts?

有什么想法吗？

回答by Justin Cave

Presumably, since you're not providing a value for the DB_IDcolumn, 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_IDthat is currently in the table leading to the error.

由于数据是从生产数据库中移动的（大概是最近），我敢打赌，当数据被复制时，序列也没有被修改。我猜测该序列生成的值远低于DB_ID表中当前导致错误的最大值。

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 INCREMENTto something very large, get the nextval once, and set the INCREMENTback to 1.

如果，正如我怀疑的那样，序列正在生成数据库中已经存在的值，您可以增加序列直到它生成未使用的值，或者您可以更改它以将其设置INCREMENT为非常大的值，获取 nextval 一次，然后设置INCREMENT回到 1。

回答by Brett McCann

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.

看起来您没有为主键字段 DB_ID 提供值。如果这是主键，则必须为该列提供唯一值。不提供它的唯一方法是创建一个数据库触发器，该触发器在插入时将提供一个值，很可能是从序列派生的。

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.

如果这是从另一个数据库恢复，并且这个新实例上有一个序列，则它可能试图重用一个值。如果旧数据具有 1 - 1000 之间的唯一键，并且您当前的序列为 500，则它将生成已存在的值。如果此表确实存在序列并且它正在尝试使用它，则您需要将表中的值与序列的当前值进行协调。

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

您可以使用 SEQUENCE_NAME.CURRVAL 查看序列的当前值（当然如果它存在）

回答by Christopher Rayl

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.

您的错误看起来像是在复制数据库中已经存在的主键。您应该修改您的 sql 代码以使用诸如 IDENTITY 关键字之类的东西来实现其自己的主键。

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

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

);