Violation of Primary Key Contraint
-
Hello,
I am getting this error when executing my statement:
Msg 2627, Level 14, State 1, Procedure ad_MissingStudentMasterData_Enrolled, Line 89Violation of PRIMARY KEY constraint 'CmEvent_CmEventID_U_C_IDX'. Cannot insert duplicate key in object 'dbo.CmEvent'. The duplicate key value is (52001148).
CmEventID is the Primary key of the CmEvent table. I only inserted columns that were "not null" and did not insert cmEventID which is the primary key. There are no constraint for this table that I am seeing.
WITH #ExList AS
(
SELECT DISTINCT st.SystudentID
,CASE WHEN SSN = ''
THEN 1
ELSE 0
END AS Social
,CASE WHEN AmPrevEducID = 0
THEN 1
ELSE 0
END AS PrevEd
,CASE WHEN sar.AmRaceID IS NULL
THEN 1
ELSE 0
END AS Race
,CASE WHEN AmSexID = 0
THEN 1
ELSE 0
END AS Gender
,CASE WHEN DOB IS NULL
THEN 1
ELSE 0
END AS DOB
,CASE WHEN Addr1 LIKE '%Address%' ORCity = '' OR [State] = '' OR Zip = ''
THEN 1
ELSE 0
END AS 'Address'
,CASE WHEN AdShiftID = 0
THEN 1
ELSE 0
END AS [Shift]
FROM Systudent st
LEFT OUTER JOIN SyStudentAmRace sar ONst.SyStudentId = sar.SyStudentID
WHERE SyschoolstatusID IN (88)
),
--Final List to Exclude from Process
#ExListFinal AS
( SELECTDISTINCT SyStudentID
,1 ChangeExclude
FROM #ExList l
WHERE Social =1 OR PrevEd = 1 OR Race =1 OR Gender = 1 OR DOB = 1 OR [Address] = 1 OR [Shift] = 1
)
--Create Activity for students missinginformation on Student Master
INSERT INTO CMEvent
([DueDate]
,[Comments]
,[Subject]
,[Priority]
,[CmTemplateID]
,[CmEventTypeID]
,[CmEventStatusID]
,[StatusDate]
,[CmEventResultID]
,[SourceRecordID]
,[SourceTable]
,[SetupBy]
,[CompletedBy]
,[TemplateTextFile] --Add by Chad Holmes6/20/18, not null column
,[UserID]
,[DateAdded]
,[DateLstMod]
,[ModFlag]
,[SyStaffID]
,[SyStudentID]
,[ActivityNotViewed]--Add by Chad Holmes 6/20/18
,[SourceSystem]
,[CrmSuppressFlag]
)
SELECTDISTINCT GetDate() [DueDate]
,CASE WHEN (l.Social+l.PrevEd+l.Race+l.Gender+l.DOB+l.[Address]) >1
THEN 'Student Master is missing thestudents multiple student demographic fields'
WHEN l.Social = 1
THEN 'Student Master is missing thestudents SSN'
WHEN l.PrevEd = 1
THEN 'Student Master is missing thestudents Previous Education'
WHEN l.Race = 1
THEN 'Student Master is missing thestudents Race specification'
WHEN l.Gender =1
THEN 'Student Master is missing thestudents Gender designation'
WHEN l.DOB =1
THEN 'Student Master is missing thestudents DOB'
WHEN [Address] = 1
THEN 'Student Master is missing thestudents Address data'
WHEN [Shift] = 1
THEN 'Student Master is missing thestudents Shift data'
END AS [Comments]
,'StudentMaster Data Missing' [Subject]
,'Normal' [Priority]
,705 [CmTemplateID]
,4 [CmEventTypeID]
,1 [CmEventStatusID]
,GetDate() [StatusDate]
,0 [CmEventResultID]
,0 [SourceRecordID]
,'' [SourceTable]
,1 [SetupBy]
,0[CompletedBy]
,''[TemplateTextFile]
,1 [UserID]
,GetDate() [DateAdded]
,GetDate()[DateLstMod]
,'A' [ModFlag]
,st.AmRepID [SyStaffID]
,0[ActivityNotViewed]
,el.[SyStudentID]
,'C' [SourceSystem]
,0 [CrmSuppressFlag]
FROM#ExListFinal el (NOLOCK)
LEFTOUTER JOIN #ExList l (NOLOCK) ON el.SyStudentID = l.SystudentID
INNERJOIN systudent st (NOLOCK) ON el.SyStudentID = st.SyStudentId
WHEREel.SyStudentID NOT IN (SELECT SystudentID FROM CmEvent WHERE CmTemplateID = 705AND CmEventStatusID IN (1)) --No Pending RC Student Audit Activity
-
Query that is a (little more) readable (white space remove courtesy of SSC's broken text editor) and with the syntax errors removed:
WITH
#ExList AS
(SELECT DISTINCT
st.SystudentID,
CASE WHEN SSN = '' THEN 1 ELSE 0 END AS Social,
CASE WHEN AmPrevEducID = 0 THEN 1 ELSE 0 END AS PrevEd,
CASE WHEN sar.AmRaceID IS NULL THEN 1 ELSE 0 END AS Race,
CASE WHEN AmSexID = 0 THEN 1 ELSE 0 END AS Gender,
CASE WHEN DOB IS NULL THEN 1 ELSE 0 END AS DOB,
CASE WHEN Addr1 LIKE '%Address%'
OR City = ''
OR [State] = ''
OR Zip = '' THEN
1
ELSE 0
END AS 'Address',
CASE WHEN AdShiftID = 0 THEN 1 ELSE 0 END AS [Shift]
FROM Systudent st
LEFT OUTER JOIN SyStudentAmRace sar ON st.SyStudentId = sar.SyStudentID
WHERE SyschoolstatusID IN (88)),--Final List to Exclude from Process
#ExListFinal AS
(SELECT DISTINCT
SyStudentID,
1 ChangeExclude
FROM #ExList l
WHERE Social = 1
OR PrevEd = 1
OR Race = 1
OR Gender = 1
OR DOB = 1
OR [Address] = 1
OR [Shift] = 1)--Create Activity for students missinginformation on Student Master
INSERT INTO CMEvent ([DueDate],
[Comments],
[Subject],
[Priority],
[CmTemplateID],
[CmEventTypeID],
[CmEventStatusID],
[StatusDate],
[CmEventResultID],
[SourceRecordID],
[SourceTable],
[SetupBy],
[CompletedBy],
[TemplateTextFile], --Add by Chad Holmes6/20/18, not null column
[UserID],
[DateAdded],
[DateLstMod],
[ModFlag],
[SyStaffID],
[SyStudentID],
[ActivityNotViewed], --Add by Chad Holmes 6/20/18
[SourceSystem],
[CrmSuppressFlag])
SELECT DISTINCT
GETDATE() [DueDate],
CASE WHEN (l.Social + l.PrevEd + l.Race + l.Gender + l.DOB + l.[Address]) > 1 THEN
'Student Master is missing thestudents multiple student demographic fields'
WHEN l.Social = 1 THEN
'Student Master is missing thestudents SSN'
WHEN l.PrevEd = 1 THEN
'Student Master is missing thestudents Previous Education'
WHEN l.Race = 1 THEN
'Student Master is missing thestudents Race specification'
WHEN l.Gender = 1 THEN
'Student Master is missing thestudents Gender designation'
WHEN l.DOB = 1 THEN
'Student Master is missing thestudents DOB'
WHEN [Address] = 1 THEN
'Student Master is missing thestudents Address data'
WHEN [Shift] = 1 THEN
'Student Master is missing thestudents Shift data'
END AS [Comments],
'StudentMaster Data Missing' [Subject],
'Normal' [Priority],
705 [CmTemplateID],
4 [CmEventTypeID],
1 [CmEventStatusID],
GETDATE() [StatusDate],
0 [CmEventResultID],
0 [SourceRecordID],
'' [SourceTable],
1 [SetupBy],
0 [CompletedBy],
'' [TemplateTextFile],
1 [UserID],
GETDATE() [DateAdded],
GETDATE() [DateLstMod],
'A' [ModFlag],
st.AmRepID [SyStaffID],
0 [ActivityNotViewed],
el.[SyStudentID],
'C' [SourceSystem],
0 [CrmSuppressFlag]
FROM #ExListFinal el (NOLOCK)
LEFT OUTER JOIN #ExList l (NOLOCK) ON el.SyStudentID = l.SystudentID
INNER JOIN systudent st (NOLOCK) ON el.SyStudentID = st.SyStudentId
WHERE el.SyStudentID NOT IN (SELECT SystudentID
FROM CmEvent
WHERE CmTemplateID = 705
AND CmEventStatusID IN (1)); --No Pending RC Student Audit ActivityNow, more on topic. Firstly, i notice the use of NOLOCK here, what's your reason behind that?
In relation to your error, we don't have the DDL for your table, so this is guesswork right now. Is CmEventID a IDENTITY column? If so, I would guess that a row has been deleted at some point, and someone has reseeded the IDENTITY value to a number that is lower than an existing value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, June 21, 2018 5:20 AM
Exactly the questions I was going to ask. Please provide the CREATE TABLE script for dbo.CmEvent.
If CmEventID is an IDENTITY column, please provide the results of running the following:
SELECT MAX(CmEventID) from dbo.CmEvent--and
DBCC CHECKIDENT ('dbo.CmEvent', NORESEED)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
USE [campus_reporting]
GO
/****** Object: Table [dbo].[CmEvent] Script Date: 6/21/2018 8:03:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CmEvent](
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[RemindDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[Comments] [text] NOT NULL,
[Subject] [varchar](255) NULL,
[Priority] [char](10) NOT NULL,
[CmTemplateID] [int] NOT NULL,
[CmEventTypeID] [int] NOT NULL,
[CmEventStatusID] [int] NOT NULL,
[StatusDate] [datetime] NULL,
[CmEventResultID] [int] NOT NULL,
[SourceRecordID] [int] NOT NULL,
[SourceTable] [char](20) NOT NULL,
[ResultText] [varchar](255) NULL,
[SetupBy] [int] NOT NULL,
[CompletedBy] [int] NOT NULL,
[TemplateTextFile] [varchar](50) NOT NULL,
[UserID] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ModFlag] [char](1) NOT NULL,
[ts] [timestamp] NOT NULL,
[CmEventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PlEmployerID] [int] NULL,
[SyStaffID] [int] NULL,
[SyStudentID] [int] NULL,
[SyStudentInquiryID] [int] NULL,
[Attachments] [varchar](1280) NULL,
[PlEmployerJobID] [int] NULL,
[PlEmployerContactID] [int] NULL,
[AdEnrollID] [int] NULL,
[SessionSyCampusID] [int] NULL,
[ActivityNotViewed] [bit] NOT NULL,
[ReassignedDate] [datetime] NULL,
[SyOrganizationID] [int] NULL,
[SyOrganizationContactID] [int] NULL,
[EmailSubject] [varchar](255) NULL,
[SourceSystem] [char](1) NOT NULL,
[PrevSyStaffID] [int] NULL,
[LockedBy] [int] NULL,
[CmAttachmentsId] [int] NULL,
[CrmSuppressFlag] [bit] NOT NULL,
[WorkflowInstanceId] [uniqueidentifier] NULL,
[AdClassSchedID] [int] NULL,
CONSTRAINT [CmEvent_CmEventID_U_C_IDX] PRIMARY KEY CLUSTERED
(
[CmEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [C2000_CmEvent]
) ON [C2000_CmEvent] TEXTIMAGE_ON [C2000_CmEvent]
GO
SET ANSI_PADDING ON
GO
The MAX cmEvent ID returned 55845900
-
Checking identity information: current identity value '52001149', current column value '55845902'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- Phil Parkin - Thursday, June 21, 2018 5:26 AM
Checking identity information: current identity value '52001149', current column value '55845902'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-
Something a bit more readable (again):
USE [campus_reporting];
GOCREATE TABLE [dbo].[CmEvent] ([StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[RemindDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[Comments] [text] NOT NULL,
[Subject] [varchar](255) NULL,
[Priority] [char](10) NOT NULL,
[CmTemplateID] [int] NOT NULL,
[CmEventTypeID] [int] NOT NULL,
[CmEventStatusID] [int] NOT NULL,
[StatusDate] [datetime] NULL,
[CmEventResultID] [int] NOT NULL,
[SourceRecordID] [int] NOT NULL,
[SourceTable] [char](20) NOT NULL,
[ResultText] [varchar](255) NULL,
[SetupBy] [int] NOT NULL,
[CompletedBy] [int] NOT NULL,
[TemplateTextFile] [varchar](50) NOT NULL,
[UserID] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ModFlag] [char](1) NOT NULL,
[ts] [timestamp] NOT NULL,
[CmEventID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[PlEmployerID] [int] NULL,
[SyStaffID] [int] NULL,
[SyStudentID] [int] NULL,
[SyStudentInquiryID] [int] NULL,
[Attachments] [varchar](1280) NULL,
[PlEmployerJobID] [int] NULL,
[PlEmployerContactID] [int] NULL,
[AdEnrollID] [int] NULL,
[SessionSyCampusID] [int] NULL,
[ActivityNotViewed] [bit] NOT NULL,
[ReassignedDate] [datetime] NULL,
[SyOrganizationID] [int] NULL,
[SyOrganizationContactID] [int] NULL,
[EmailSubject] [varchar](255) NULL,
[SourceSystem] [char](1) NOT NULL,
[PrevSyStaffID] [int] NULL,
[LockedBy] [int] NULL,
[CmAttachmentsId] [int] NULL,
[CrmSuppressFlag] [bit] NOT NULL,
[WorkflowInstanceId] [uniqueidentifier] NULL,
[AdClassSchedID] [int] NULL,
CONSTRAINT [CmEvent_CmEventID_U_C_IDX]
PRIMARY KEY CLUSTERED ([CmEventID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [C2000_CmEvent]) ON [C2000_CmEvent] TEXTIMAGE_ON [C2000_CmEvent];
Have you tried running your original statement again? Considering that the IDENTITY is now at 55845902, then the problem may have resolved itself now.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, June 21, 2018 6:59 AM
When ever I run the statement again the "duplicate key value" just goes up by one.
- chad.m.holmes - Thursday, June 21, 2018 7:03 AM
Do you have a trigger or anything on the table? If so post the DDL (and please try to post it in a readable format. 5/6 lines of white space between each line of SQL really doesn't help).
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Also, I noticed you haven't responded on my comment in regards to NOLOCK. What's your reasoning for using it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, June 21, 2018 7:10 AM
No constraints or triggers
- Thom A - Thursday, June 21, 2018 7:18 AM
I removed NOLOCK and I am getting the same result.
- chad.m.holmes - Thursday, June 21, 2018 6:06 AM
DUDE!!! At least try to post readable code! Copy your code to NotePad and then copy from that before pasting. If you click on "SQL Code" at the bottom of the post window and paste between the starting and ending tag from that, it'll be much more tempting to try to help you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It was reposted in the second thread. I’m new to this form. I didn’t know that trick.
-
To fix the immediate problem, try running:
DBCC CHECKIDENT ('dbo.CmEvent', RESEED, 55845910)
Better yet, rid yourself of the foolish and damaging myth that all tables should be clustered by identity by default, and cluster such logging tables first on StartDate. You can use add the identity, ( StartDate, $IDENTITY ), if you want to make a PK rather than just a standard CLUSTERED INDEX.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply