'违反 PRIMARY KEY 约束' SQL 错误

2022-01-09 00:00:00 sql-server coldfusion

这似乎是一个很常见的问题，但是到目前为止我尝试过的所有事情都没有解决方案.我正在为我的 PK 使用 ID 字段，并且自动增量已打开.这发生在数据已与当前数据合并的 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" />
                )

推荐答案

我在你的问题中注意到了这一行:

I noticed this specific line in your question:

这发生在数据已与当前数据合并的 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)

相关文章