SQL 主键约束虽然记录不存在 [英] SQL primary key constraint although record does not exist

问题描述

我收到以下错误:

违反 PRIMARY KEY 约束PK_ss_student_grade".无法在对象dbo.ss_student_grade"中插入重复键.重复键值为 (301, 1011, 24801, 33).

Violation of PRIMARY KEY constraint 'PK_ss_student_grade'. Cannot insert duplicate key in object 'dbo.ss_student_grade'. The duplicate key value is (301, 1011, 24801, 33).

如果我在插入前检查表，则没有具有此类主键的记录.

If I check the table before the insert there are no records with such a primary key.

插入是通过 C# 代码完成的，我确保代码只运行一次.即使在错误之后，如果我检查表，我仍然没有得到这样一个主键的记录.

The insert is done through C# code and I made sure that the code runs only once. even after the error if I check the table I still get no record with such a primary key.

注意:触发器在表中插入时运行，但它只写入日志文件，不影响数据库中的任何数据

Note: a trigger runs on the insert in the table but it only writes to a log file and does not affect any data in the database

什么会使 SQL 误以为密钥已经存在?

What could confuse SQL into thinking the key already exists?

代码调用执行以下操作的过程

the code calls a procedure that does the following

 insert into ss_student_grade(sg_school_code,sg_acad_year,sg_serial_no,sg_student_key,sg_original_grade,sg_grade,sg_school_grade,sg_category_type,sg_operator,sg_datetime)
 select pg_school_code, pg_acad_year, et_serial_no, mep_student_key, pg_grade,pg_grade,pg_grade,'Original', SYSTEM_USER, @ExamCreationDate
  from #StudentGrades
  where not exists (select 1 from ss_student_grade where sg_school_code = pg_school_code and sg_acad_year = pg_acad_year and sg_serial_no = et_serial_no and sg_student_key = mep_student_key)

我使用的是 SQL Server 2008 R2 和 Visual Studio 2010 Ultimate

I am using SQL Server 2008 R2 and Visual Studio 2010 Ultimate

推荐答案

我会查看临时表 #StudentGrades.它很可能包含构成您的唯一主键的数据的重复项.where not exists 不能防止这种情况，因为 SQL 是基于设置的.

I would look at the temp table #StudentGrades. It most likely contain duplicates of the data that makes up your unique primary key. The where not exists does not protect against this because SQL is set based.

您很可能需要更改 PK 的定义、更改 #StudentGrades 表的内容或更改 select 语句以提供唯一的行.

You most likely need to either change the definition of your PK, change the content of the #StudentGrades table or change the select statement to give unique rows.

这可以例如正在使用 GROUP BY 或 ROW_NUMBER() OVER(PARTITION BY column ORDER BY column)

This could e.g. be using GROUP BY, or ROW_NUMBER() OVER(PARTITION BY column ORDER BY column)

这篇关于SQL 主键约束虽然记录不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！