违反“身份"列上的主键错误 [英] Violation of Primary Key error on Identity column

查看：75
本文介绍了违反“身份"列上的主键错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这太疯狂了！有问题的代码已经运行了5年以上.

This is maddening! Code in question has been running for over 5 years.

这是独家新闻...

我正在用主键(是标识列)对表进行INSERT...SELECT.插入时我没有指定密钥-SQL Server会按预期生成它.

I am doing an INSERT...SELECT into a table with a primary key that is an identity column. I do not specify the key when I insert - SQL Server generates it as expected.

我正在循环中调用存储过程中进行插入(实际上是SSIS中的for循环).存储过程将批量插入行(可配置).它可能一次插入1000行，也可能插入50,000行-没关系.它将适用于随机数量的呼叫(插入数千行)，然后会突然失败，并显示

I am doing the insert in a stored procedure that I call in a loop (for loop in SSIS, actually). The stored procedure will insert rows in batches (configurable). It might insert 1000 rows at a time or it might insert 50,000 - doesn't matter. It will work for a random number of calls (inserting thousands of rows) and then it will fail, out of the blue, with a

违反主键/重复键

Violation of primary key / duplicate

错误.如果我检查身份种子-是正确的.如果我再次启动该过程，则将在一段时间内正常工作.

error. If I check the identity seed - it is correct. If I kick off the process again it will work fine, for a while.

要插入的值来自我联接在一起的2个表，好像很重要.

The values being inserted are coming from 2 tables that I join together, as if that matters.

我的大部分代码如下:

WHILE @pk <= @max_pk
BEGIN
   INSERT INTO tbl_claim_line (fk_batch_control_group, fk_claim, fk_provider, service_from_date, service_to_date, allowed, net_paid, COB, flex_1, flex_2, flex_3, flex_4)
      SELECT
         @fk_batch_control_group
         , c.pk_claim
         , p.pk_provider
         , i.date_of_service_from
         , i.date_of_service_to
         , i.allowed_amount
         , i.net_paid_amount
         , i.cob_amount
         , i.claimline_flex_1
         , i.claimline_flex_2
         , i.claimline_flex_3
         , i.claimline_flex_4
      FROM
         tbl_import i
      INNER JOIN 
         tbl_import__claim c ON i.claim_number = c.claim_number
      LEFT JOIN 
         tbl_import__provider p ON  ISNULL(i.provider_type,'') =  ISNULL(p.provider_type,'') 
             AND ISNULL(i.provider_specialty,'') =  ISNULL(p.provider_specialty,'') 
             AND  ISNULL(i.provider_zip_code,'') =  ISNULL(p.provider_zip_code,'')
      WHERE
          pk_import = @pk

    UPDATE tbl_import 
    SET fk_claim_line = SCOPE_IDENTITY() 
    WHERE pk_import = @pk

    SET @pk += 1
END

--TABLE DEFINITIONS...
CREATE TABLE [dbo].[tbl_claim_line](
    [fk_batch_control_group] [int] NOT NULL,
    [fk_claim] [int] NOT NULL,
    [fk_provider] [int] NULL,
    [service_from_date] [date] NULL,
    [service_to_date] [date] NULL,
    [allowed] [money] NULL,
    [net_paid] [money] NULL,
    [COB] [money] NULL,
    [flex_1] [varchar](200) NULL,
    [flex_2] [varchar](200) NULL,
    [flex_3] [varchar](200) NULL,
    [flex_4] [varchar](200) NULL,
    [pk_claim_line] [int] IDENTITY(1,1) NOT NULL,
    [insert_date] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_claim_line] PRIMARY KEY NONCLUSTERED 
(
    [pk_claim_line] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_claim_line] WITH CHECK 
ADD  CONSTRAINT [FK_tbl_claim_line_tbl_batch_control_group] 
FOREIGN KEY([fk_batch_control_group])
REFERENCES [dbo].[tbl_batch_control_group] ([pk_batch_control_group])
GO

ALTER TABLE [dbo].[tbl_claim_line] CHECK CONSTRAINT [FK_tbl_claim_line_tbl_batch_control_group]
GO

ALTER TABLE [dbo].[tbl_claim_line]  WITH CHECK 
ADD CONSTRAINT [FK_tbl_claim_line_tbl_claim] 
FOREIGN KEY([fk_claim])
REFERENCES [dbo].[tbl_claim] ([pk_claim])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tbl_claim_line] CHECK CONSTRAINT [FK_tbl_claim_line_tbl_claim]
GO

ALTER TABLE [dbo].[tbl_claim_line]  WITH CHECK 
ADD CONSTRAINT [FK_tbl_claim_line_tbl_provider] 
FOREIGN KEY([fk_provider])
REFERENCES [dbo].[tbl_provider] ([pk_provider])
GO

ALTER TABLE [dbo].[tbl_claim_line] CHECK CONSTRAINT [FK_tbl_claim_line_tbl_provider]
GO

ALTER TABLE [dbo].[tbl_claim_line] ADD  CONSTRAINT [DF_tbl_claim_line__insert_date]  DEFAULT (getdate()) FOR [insert_date]
GO

----second table
CREATE TABLE [dbo].[tbl_import](
    [fk_claim_line] [int] NULL,
    [member_id] [varchar](50) NULL,
    [member_card_id] [varchar](50) NULL,
    [member_first_name] [varchar](50) NULL,
    [member_last_name] [varchar](50) NULL,
    [member_dob] [varchar](50) NULL,
    [member_gender] [varchar](50) NULL,
    [member_subscriber_relationship_code] [varchar](50) NULL,
    [member_address_line_1] [varchar](100) NULL,
    [member_address_line_2] [varchar](100) NULL,
    [member_city] [varchar](50) NULL,
    [member_state] [varchar](50) NULL,
    [member_zip] [varchar](50) NULL,
    [member_phone] [varchar](50) NULL,
    [member_email] [varchar](50) NULL,
    [subscriber_id] [varchar](50) NULL,
    [group_line_of_business] [varchar](50) NULL,
    [group_product] [varchar](50) NULL,
    [group_employer] [varchar](50) NULL,
    [provider_first_name] [varchar](50) NULL,
    [provider_last_or_full_name] [varchar](200) NULL,
    [provider_type] [varchar](200) NULL,
    [provider_specialty] [varchar](400) NULL,
    [provider_zip_code] [varchar](50) NULL,
    [provider_tax_id] [varchar](50) NULL,
    [medical_code_1] [varchar](10) NULL,
    [medical_code_1_description] [varchar](500) NULL,
    [medical_code_2] [varchar](10) NULL,
    [medical_code_2_description] [varchar](500) NULL,
    [medical_code_3] [varchar](10) NULL,
    [medical_code_3_description] [varchar](500) NULL,
    [medical_code_4] [varchar](10) NULL,
    [medical_code_4_description] [varchar](500) NULL,
    [medical_code_5] [varchar](10) NULL,
    [medical_code_5_description] [varchar](500) NULL,
    [medical_code_6] [varchar](10) NULL,
    [medical_code_6_description] [varchar](500) NULL,
    [medical_code_7] [varchar](10) NULL,
    [medical_code_7_description] [varchar](500) NULL,
    [medical_code_8] [varchar](10) NULL,
    [medical_code_8_description] [varchar](500) NULL,
    [medical_code_9] [varchar](10) NULL,
    [medical_code_9_description] [varchar](500) NULL,
    [medical_code_10] [varchar](10) NULL,
    [medical_code_10_description] [varchar](500) NULL,
    [medical_code_11] [varchar](10) NULL,
    [medical_code_11_description] [varchar](500) NULL,
    [medical_code_12] [varchar](10) NULL,
    [medical_code_12_description] [varchar](500) NULL,
    [medical_code_13] [varchar](10) NULL,
    [medical_code_13_description] [varchar](500) NULL,
    [medical_code_14] [varchar](10) NULL,
    [medical_code_14_description] [varchar](500) NULL,
    [medical_code_15] [varchar](10) NULL,
    [medical_code_15_description] [varchar](500) NULL,
    [medical_code_16] [varchar](10) NULL,
    [medical_code_16_description] [varchar](500) NULL,
    [date_of_service_from] [varchar](50) NULL,
    [date_of_service_to] [varchar](50) NULL,
    [claim_number] [varchar](50) NULL,
    [claim_line_number] [varchar](50) NULL,
    [original_claim_number] [varchar](50) NULL,
    [allowed_amount] [varchar](50) NULL,
    [net_paid_amount] [varchar](50) NULL,
    [cob_amount] [varchar](50) NULL,
    [date_paid] [varchar](50) NULL,
    [member_flex_1] [varchar](200) NULL,
    [member_flex_2] [varchar](200) NULL,
    [member_flex_3] [varchar](200) NULL,
    [member_flex_4] [varchar](200) NULL,
    [claim_flex_1] [varchar](200) NULL,
    [claim_flex_2] [varchar](200) NULL,
    [claim_flex_3] [varchar](200) NULL,
    [claim_flex_4] [varchar](200) NULL,
    [claimline_flex_1] [varchar](200) NULL,
    [claimline_flex_2] [varchar](200) NULL,
    [claimline_flex_3] [varchar](200) NULL,
    [claimline_flex_4] [varchar](200) NULL,
    [pk_import] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tbl_import] PRIMARY KEY NONCLUSTERED 
(
    [pk_import] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

推荐答案

我遇到了这个问题，很像user3170349，这是专栏中的种子问题.不过，我要添加一些其他信息.

I ran into this and much like user3170349, it was a seed issue on the column. I'm adding some additional info, however.

首先，您可以运行此命令来确定是否有种子问题:

First, you can run this to figure out if you have a seed problem:

DBCC CHECKIDENT ('TABLE_NAME_GOES_HERE', NORESEED);

这将为您提供信息，内容如下:

This will give you information which will read something like this:

Checking identity information: current identity value 'XXXX', current column value 'YYYY'.

如果YYYY大于XXXX，则您有问题，需要重新放置表才能使事情重新进行.您可以使用以下命令进行操作:

If YYYY is larger than XXXX, then you have a problem and need to RESEED the table to get things going again. You can do so with the following command:

DBCC CHECKIDENT ('TABLE_NAME_GOES_HERE', RESEED, ZZZZZ);

其中ZZZZ是种子值.该值应至少比YYYY高1. YMMV，因此请选择一个适合您情况的值.

Where ZZZZ is the reseed value. That value should be at least one higher than YYYY. YMMV, so pick a value that is appropriate for your situation.

这篇关于违反“身份"列上的主键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆