如何解决违反来自sp？的PRIMARY KEY约束错误？ [英] How to solve Violation of PRIMARY KEY constraint error which is coming from sp ?
本文介绍了如何解决违反来自sp？的PRIMARY KEY约束错误？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
来自我的c＃代码的
当我尝试调用我的程序时，我收到以下错误。
我试图调试它确实错误到了哪里？
{违反PRIMARY KEY约束PK__ ＃B12AD42__19093A2B4B4AF11E。无法在对象dbo中插入重复键。@ TCategory。重复键值为（0）。\\\\ n语句已终止。}
ALTER PROCEDURE [dbo]。[spGetAllProjectAndTaskForUser]
@EmpID INT
， @ UserID int
， @ ProjectName NVARCHAR （MAX）
， @ Status XML
， @ Category XML
， @ Priority XML
， @ Location XML
， @IsAdvanceSearch BIT = NULL
， @ StartDate 日期时间
， @ EndDate DateTime
， @ CreUserEmpID INT
AS
SET NOCOUNT ON
< span class =code-keyword> BEGIN
DECLARE @ TStatus < span class =code-keyword> TABLE （StatusID INT PRIMARY KEY ）
DECLARE @ TCategory TABLE （CategoryID INT PRIMARY KEY ）
DECLARE @ TPriority TABLE （PriorityID INT PRIMARY KEY ）
IF （ @ Status IS NOT NULL ）
BEGIN
INSERT INTO @ TStatus （StatusID）
SELECT DataTable.ItemCol.value（' C1 [1]'，' int'）
FROM @状态 .nodes（' // T / R' ） AS DataTable（ItemCol）
END
ELSE
BEGIN
INSERT INTO @ TStatus （StatusID）
SELECT DISTINCT l.LookupID FROM [查询] l
INNER JOIN LookupType lt ON l。类型 = lt.LookupTypeID
WHERE lt.Code = ' TaskStatus'
END
IF （ @ Category IS NOT NULL ）
BEGIN
INSERT INTO @ TCategory （CategoryID）
SELECT DataTable.ItemCol.value（' C1 [1]'，' int'）
FROM @ Category .nodes（' < span class =code-string> // T / R'） AS DataTable（ItemCol）
结束
ELSE
BEGIN
INSERT INTO @ TCategory （类别ID）
SELECT ToDoCategoryId From ToDoCategory
结束
IF （ @ Priority IS NOT NULL ）
BEGIN
INSERT INTO @ TPriority （PriorityID）
SELECT DataTable.ItemCol.value（ ' C1 [1]'，' int'）
FROM @ Priority .nodes（' // T / R'） AS Da taTable（ItemCol）
END
ELSE
BEGIN
INSERT INTO @ TPriority （PriorityID）
SELECT DISTINCT l。 LookupID FROM [查询] l
INNER 加入 LookupType lt ON l。类型 = lt.LookupTypeID
WHERE lt.Code = ' TaskPriority'
END
SELECT
（' P' + CAST（p.ProjectID as Varchar （ 10 ））） AS ID，
p.ProjectID，
0 AS TaskID，
p.Name，
p.Detail，
p.StartDate，
p.EndDate，
GetDate（） AS AlertDate，
p.PriorityId as PriorityId，
lp。 Type as PriorityType，
lp.Code as PriorityCode，
lp 。文本 as PriorityText，
p.StatusId as StatusId，
ls。 Type as StatusType，
ls .Code AS StatusCode，
ls。 Text AS StatusText，
p.CategoryID as CategoryID，
lc.ToDoCategoryID as CategoryType，
lc.Code AS CategoryCode，
lc.Name AS CategoryText，
p.Pecentage AS 完成，
null < span class =code-keyword> AS 重量，
null AS ParentID，
0 AS 类型，
p.CustomerID，
c.FirstName + ' ' + c .LastName AS CustomerName，
p.CustomerLocationID，
l.Name AS LocationName ，
ISNULL（c.FirstName，' '）+ ' ' + ISNULL（c.LastName，' '）+ ' ' + ISNULL（l.Name，' '） AS Assosiation
FROM 项目p
INNER JOIN [ToDoCategory] lc on p.CategoryID = lc.ToDoCategoryID
INNER JOIN [查询] ls on p.StatusId = ls.LookupID
INNER JOIN [查询] 上的 p.PriorityId = lp.LookupID
INNER JOIN @ TCategory lct ON p。 CategoryID = lct.CategoryID
INNER JOIN @ TStatus lcs ON p.StatusId = lcs.StatusID
INNER JOIN @ TPriority lcp ON p.PriorityID = lcp。 PriorityID
LEFT OUTER JOIN 客户c on p.CustomerID = c.CustomerID
LEFT OUTER JOIN CustomerLocation l on p.CustomerLocationID = l.CustomerLocationID
WHERE p.EmployeeID = @EmpID AND p。 IsInActive = 0
AND （ @ ProjectName IS NULL 或（p .Name LIKE ' ％' + < span class =code-sdkkeyword> @ ProjectName + ' ％' OR p.Detail LIKE ' ％' + @ ProjectName + ' ％'））
AND （ @ StartDate IS NULL 或 p.StartDate > = @ StartDate ）
AND （ @EndDate IS NULL OR （p.EndDate IS NULL 或 p.EndDate< = @ EndDate ））
AND （（<（span class = code-sdkkeyword> @ IsAdvanceSearch IS NULL OR @ IsAdvanceSearch = 0 ）和 p.Pecentage< 100 ）或 @ IsAdvanceSearch = 1 ）
UNION ALL
SELECT （' T' + CAST（t.TaskId as Varchar （ 10 ））） AS ID，t.ProjectID AS ProjectID，t.TaskId，
t .Name，t.Notes，t.StartDate，t.EndDate，AlertDate，
t.PriorityId，lp。 Type as PriorityType，lp.Code AS PriorityCode，lp。 Text AS PriorityText，
t.StatusId，ls。 Type AS StatusType，ls.Code AS StatusCode，ls。 Text < span class =code-keyword> AS StatusText，
0 as CategoryID，' ' as CategoryType，< span class =code-string>' ' AS CategoryCode，' ' AS CategoryText，
t.Pecentage AS 完成，t.Weigtage AS 权重，（' P' + CAST（p.ProjectID as Varchar （ 10 ））） As Pa rentID， 1 AS 类型，
0 AS CustomerID，' < span class =code-string>' AS 客户名称，
0 AS LocationID，' ' AS LocationName，' ' AS Assosiation
FROM 项目p
INNER JOIN 任务t on t.ProjectId = p.ProjectId
INNER JOIN [查询] lp t.PriorityId = lp.LookupID
INNER JOIN [查询] ls on t.StatusId = ls.LookupID
INNER JOIN @ TCategory lct ON p.CategoryID = lct.CategoryID
INNER JOIN @ TStatus lcs ON t.StatusId = lcs.StatusID
INNER JOIN @ TPriority lcp ON t.PriorityID = lcp.PriorityID
WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0 AND t.IsInActive = 0
AND （ @ ProjectName IS NULL OR （p.Name LIKE ' ％' + @ ProjectName + ' ％' 或 p.Detail LIKE ' ％' + @ ProjectName + ' ％'））
AND （ @ StartDate IS NULL OR p.StartDate> = @ StartDate ）
AND （< span class =code-sdkkeyword> @ EndDate IS NULL OR （p.EndDate IS NULL OR p.EndDate< = @ EndDate ））
AND （（（ @ IsAdvanceSearch IS NULL 或 @ IsAdvanceSearch = 0 ）< span class =code-keyword> AND p.Pecentage< 100 ）或 @ IsAdvanceSearch = 1 ）
END
GO
解决方案
正如 d @ nish 所解释的那样，您在@中插入重复的类别TCategory表。
快速解决方案可能是在选择查询中添加 distinct 子句
INSERT INTO @TCategory （CategoryID）
SELECT DISTINCT DataTable.ItemCol.value（ ' C1 [1]'，' int'）
FROM @ Category .nodes（' // T / R'） AS DataTable（ItemCol）
如消息所示，您试图在 @TCategory <中插入重复值/ code>表是不允许的。在代码中查看此表的所有insert语句。
在真正插入之前检查重复项可能是个好主意。如果运行select语句是出于任何原因而被视为开销的情况，您可以在insert命令周围添加异常处理，并根据需要处理这些异常。
All in the错误信息：
Quote：{违反PRIMARY KEY约束PK __＃B12AD42__19093A2B4B4AF11E。无法在对象dbo中插入重复键。@ TCategory。重复键值为（0）。\\\\ n语句已终止。}
您的字段具有
PRIMARY KEY约束。这意味着每个行的字段必须具有不同的值。
此字段已经有一个值为（0）的行，并且您尝试将第二行设置为具有相同的值，它是禁止的。
Hi ,
from my c# code I am getting below error when i try to call my procedure.
I tried to debug it but not getting where is the mistake is exactly ?
{Violation of PRIMARY KEY constraint PK__#B12AD42__19093A2B4B4AF11E. Cannot insert duplicate key in object dbo.@TCategory. The duplicate key value is (0).\r\nThe statement has been terminated.}
ALTER PROCEDURE [dbo].[spGetAllProjectAndTaskForUser]
@EmpID INT
, @UserID int
, @ProjectName NVARCHAR(MAX)
, @Status XML
, @Category XML
, @Priority XML
, @Location XML
, @IsAdvanceSearch BIT = NULL
, @StartDate DateTime
, @EndDate DateTime
, @CreUserEmpID INT
AS
SET NOCOUNT ON
BEGIN
DECLARE @TStatus TABLE(StatusID INT PRIMARY KEY)
DECLARE @TCategory TABLE(CategoryID INT PRIMARY KEY)
DECLARE @TPriority TABLE(PriorityID INT PRIMARY KEY)
IF (@Status IS NOT NULL)
BEGIN
INSERT INTO @TStatus(StatusID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Status.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TStatus(StatusID)
SELECT DISTINCT l.LookupID FROM [Lookup] l
INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID
WHERE lt.Code = 'TaskStatus'
END
IF (@Category IS NOT NULL)
BEGIN
INSERT INTO @TCategory(CategoryID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Category.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TCategory(CategoryID)
SELECT ToDoCategoryId From ToDoCategory
END
IF (@Priority IS NOT NULL)
BEGIN
INSERT INTO @TPriority(PriorityID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Priority.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TPriority(PriorityID)
SELECT DISTINCT l.LookupID FROM [Lookup] l
INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID
WHERE lt.Code = 'TaskPriority'
END
SELECT
('P' + CAST(p.ProjectID as Varchar(10))) AS ID,
p.ProjectID,
0 AS TaskID,
p.Name,
p.Detail,
p.StartDate,
p.EndDate,
GetDate() AS AlertDate,
p.PriorityId as PriorityId,
lp.Type as PriorityType,
lp.Code as PriorityCode,
lp.Text as PriorityText,
p.StatusId as StatusId,
ls.Type as StatusType,
ls.Code AS StatusCode,
ls.Text AS StatusText,
p.CategoryID as CategoryID,
lc.ToDoCategoryID as CategoryType,
lc.Code AS CategoryCode,
lc.Name AS CategoryText,
p.Pecentage AS Complete,
null AS Weightage,
null AS ParentID ,
0 AS Type ,
p.CustomerID,
c.FirstName + ' ' + c.LastName AS CustomerName,
p.CustomerLocationID,
l.Name AS LocationName,
ISNULL(c.FirstName,'') + ' ' + ISNULL(c.LastName,'') + ' ' + ISNULL(l.Name,'') AS Assosiation
FROM Project p
INNER JOIN [ToDoCategory] lc on p.CategoryID = lc.ToDoCategoryID
INNER JOIN [Lookup] ls on p.StatusId = ls.LookupID
INNER JOIN [Lookup] lp on p.PriorityId = lp.LookupID
INNER JOIN @TCategory lct ON p.CategoryID = lct.CategoryID
INNER JOIN @TStatus lcs ON p.StatusId = lcs.StatusID
INNER JOIN @TPriority lcp ON p.PriorityID = lcp.PriorityID
LEFT OUTER JOIN Customer c on p.CustomerID = c.CustomerID
LEFT OUTER JOIN CustomerLocation l on p.CustomerLocationID = l.CustomerLocationID
WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0
AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
UNION ALL
SELECT ('T' + CAST(t.TaskId as Varchar(10))) AS ID,t.ProjectID AS ProjectID,t.TaskId,
t.Name,t.Notes,t.StartDate,t.EndDate,AlertDate,
t.PriorityId,lp.Type as PriorityType,lp.Code AS PriorityCode,lp.Text AS PriorityText,
t.StatusId,ls.Type AS StatusType,ls.Code AS StatusCode,ls.Text AS StatusText,
0 as CategoryID, '' as CategoryType, '' AS CategoryCode,'' AS CategoryText,
t.Pecentage AS Complete, t.Weigtage AS Weightage, ('P' + CAST(p.ProjectID as Varchar(10))) As ParentID, 1 AS Type,
0 AS CustomerID, '' AS CustomerName,
0 AS LocationID, '' AS LocationName, '' AS Assosiation
FROM Project p
INNER JOIN Task t on t.ProjectId = p.ProjectId
INNER JOIN [Lookup] lp on t.PriorityId = lp.LookupID
INNER JOIN [Lookup] ls on t.StatusId = ls.LookupID
INNER JOIN @TCategory lct ON p.CategoryID = lct.CategoryID
INNER JOIN @TStatus lcs ON t.StatusId = lcs.StatusID
INNER JOIN @TPriority lcp ON t.PriorityID = lcp.PriorityID
WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0 AND t.IsInActive = 0
AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
END
GO 解决方案
As explained by d@nish you are inserting duplicate categories in @TCategory table.
A quick solution could be to add a distinct clause in the select query
INSERT INTO @TCategory(CategoryID) SELECT DISTINCT DataTable.ItemCol.value('C1[1]','int') FROM @Category.nodes('//T/R') AS DataTable(ItemCol)
As the message states, you are trying to insert duplicate values in@TCategorytable which is not allowed. Take a look at all the insert statements for this table in your code.
It might be a good idea to check for duplicates before really doing the insert. In case running select statement is something you see as an overhead for whatever reasons, you can add exception handling around the insert commands and handle exceptions like these as per your need.
All is in the error message:
Quote:{Violation of PRIMARY KEY constraint PK__#B12AD42__19093A2B4B4AF11E. Cannot insert duplicate key in object dbo.@TCategory. The duplicate key value is (0).\r\nThe statement has been terminated.}
You have a field with a
PRIMARY KEYconstraint. It means that each row must have a different value for this fields.
You already have a row with value (0) for this field and you try to put a second row with the same value, it is forbidden.
这篇关于如何解决违反来自sp？的PRIMARY KEY约束错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
