使用INSERT DEFAULT时出现重复键错误 [英] Duplicate Key error when using INSERT DEFAULT

问题描述

当我尝试INSERT记录时，我收到一个重复的键错误，DB2 SQL错误：SQLCODE = -803，SQLSTATE = 23505。主键是一列，INTEGER 4，Generated，它是第一列。

插入如下所示：INSERT INTO SCHEMA.TABLE1 values（DEFAULT，？ ，？，...）

我的理解是，使用值DEFAULT只会让DB2在插入时自动生成密钥，这就是我想要的。这个工作大部分时间，但有时/随机我得到重复的键错误。想法？

更具体地说，我正在针对DB2 9.7.0.3运行，使用Scriptella将一堆记录从一个数据库复制到另一个数据库。有时我可以处理一堆没有任何问题，其他时候我会立即得到错误，其他时间在2个记录，20个记录或30个记录等之后。似乎不是一个模式，也不是一样每次记录如果我更改数据来复制1条记录，而不是一堆，有时我会收到错误一次，那么下次很好。

我以为也可能有其他一些进程在我的批处理程序中插入记录，同时创建密钥。但是，我复制TO的表不应该有任何其他用户/进程在同一时间框架内尝试插入记录，尽管可能会发生READS。

编辑：添加创建信息：

 创建表SCHEMA.TABLE1（
 SYSTEM_USER_KEY INTEGER NOT NULL 
默认生成作为身份（从1增加1缓存20开始），
 COL2 ...，
）
 
 alter table SCHEMA.TABLE1 
添加约束SYSTEM_USER_SYSTEM_USER_KEY_IDX 
主键（SYSTEM_USER_KEY）; 
  

解决方案

您最有可能在表中记录ID，大于您身份序列中的下一个值。要了解您的序列的当前值，请运行以下查询。

  select s.nextcachefirstvalue-s.cache ，s.nextcachefirstvalue-s.increment 
从syscat.COLIDENTATTRIBUTES作为内部连接syscat.sequences as a on.seqid = s.seqid 
其中a.tabschema ='SCHEMA'
和a.TABNAME ='TABLE1'
和a.COLNAME ='SYSTEM_USER_KEY'
  

所以基本上发生了什么事情，不知何故，你在表中记录了ID大于身份序列的当前最后一个值的ID。所以迟早会发生与身份生成的ID相冲突。

有什么不同的原因可能会发生什么。一种可能性是加载了已经包含id列的值的数据，或者记录被插入了ID的实际值。另一个选择是，身份序列被重置为以比表中的最大值更低的值开始。

I am getting a duplicate key error, DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, when I try to INSERT records. The primary key is one column, INTEGER 4, Generated, and it is the first column.

the insert looks like this: INSERT INTO SCHEMA.TABLE1 values (DEFAULT, ?, ?, ...)

It's my understanding that using the value DEFAULT will just let DB2 auto-generate the key at the time of insert, which is what I want. This works most of the time, but sometimes/randomly I get the duplicate key error. Thoughts?

More specifically, I'm running against DB2 9.7.0.3, using Scriptella to copy a bunch of records from one database to another. Sometimes I can process a bunch with no problems, other times I'll get the error right away, other times after 2 records, or 20 records, or 30 records, etc. Does not seem to be a pattern, nor is it the same record every time. If I change the data to copy 1 record instead of a bunch, sometimes I'll get the error one time, then it's fine the next time.

I thought maybe some other process was inserting records during my batch program, and creating keys at the same time. However, the tables I'm copying TO should not have any other users/processes trying to INSERT records during this same time frame, although there could be READS happening.

Edit: adding create info:

Create table SCHEMA.TABLE1 (
  SYSTEM_USER_KEY   INTEGER   NOT NULL
    generated by default as identity (start with 1  increment by 1  cache 20), 
  COL2...,
)

alter table SCHEMA.TABLE1
    add constraint SYSTEM_USER_SYSTEM_USER_KEY_IDX
    Primary Key (SYSTEM_USER_KEY);

解决方案

You most likely have records in your table with IDs that are bigger then the next value in your identity sequence. To find out what the current value your sequence is about at, run the following query.

select s.nextcachefirstvalue-s.cache, s.nextcachefirstvalue-s.increment 
from syscat.COLIDENTATTRIBUTES as a inner join syscat.sequences as s on a.seqid=s.seqid 
where a.tabschema='SCHEMA' 
  and a.TABNAME='TABLE1' 
  and a.COLNAME='SYSTEM_USER_KEY'

So basically what happened is that somehow you got records in your table with ids that are bigger then the current last value of your identity sequence. So sooner or later these ids will collide with identity generated ids.

There are different reasons on how this could have happened. One possibility is that data was loaded which already contained values for the id column or that records were inserted with an actual value for the ID. Another option is that the identity sequence was reset to start at a lower value than the max id in the table.

这篇关于使用INSERT DEFAULT时出现重复键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！