'违反PRIMARY KEY约束'SQL错误 [英] 'Violation of PRIMARY KEY constraint' SQL Error

问题描述

这似乎是一个很常见的问题，但问，但我迄今为止所做的一切都没有解决方案。我使用ID字段为我的PK和自动增量已打开。这发生在数据已与当前数据合并的DEV环境中。

This seems to be a pretty common question that is asked but, everything I have tried so far has left with with no solution. I am using an ID field for my PK and auto increment has been turned on. This is occurring in a DEV environment where data has been merged with the current data.

任何帮助将非常感谢。

我使用的是SQL Server。我也跑了DBCC CHECKIDENT（[ceschema.ce_attendeeCredit]）和身份似乎排队正确。我认为可能有更深层次的问题。

I am using a SQL Server. I have also ran DBCC CHECKIDENT([ceschema.ce_attendeeCredit]) and the identity seems to line up properly. I think there may be a deeper issue in play.

INSERT INTO tblpersonCredit
                (
                personID,
                CreditID,
                Amount,
                ReferenceNo,
                CreatedBy
                )
            VALUES
                (
                <cfqueryparam value="#arguments.AttendeeCredit.getAttendeeID()#" CFSQLType="cf_sql_integer" />,
                <cfqueryparam value="#arguments.AttendeeCredit.getCreditID()#" CFSQLType="cf_sql_integer" />,
                <cfqueryparam value="#arguments.AttendeeCredit.getAmount()#" CFSQLType="cf_sql_float" null="#not len(arguments.AttendeeCredit.getAmount())#" />,
                <cfqueryparam value="#arguments.AttendeeCredit.getReferenceNo()#" CFSQLType="cf_sql_varchar" null="#not len(arguments.AttendeeCredit.getReferenceNo())#" />,
                <cfqueryparam value="#arguments.AttendeeCredit.getCreatedBy()#" CFSQLType="cf_sql_integer" />
                )

推荐答案

您的问题：

这是发生在DEV环境中，其中数据已与当前数据合并。

This is occurring in a DEV environment where data has been merged with the current data.

手动将数据插入自动递增字段后，您可能需要将自动递增计数器重置为您插入的最大ID之后的值。

After manually inserting data into an autoincrement field you may need to reset the autoincrement counter to something after the maximum id that you inserted.

如何做到这一点（以及是否需要）是数据库相关的。例如在MySQL中你可以使用ALTER TABLE语句：

How you do this (and whether you need to) is database dependent. For example in MySQL you can use an ALTER TABLE statement:

ALTER TABLE tbl AUTO_INCREMENT = 10000

重置SQL Server IDENTITY列的种子的等效命令是：

The equivalent command to reset the seed for an SQL Server IDENTITY column is:

DBCC CHECKIDENT(tbl, RESEED, 9999)

这篇关于'违反PRIMARY KEY约束'SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！