Violation of Primary Key constraint error

Question

I am using SSMS 2008 and trying to insert with this query but am getting the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_j5c_MasterMeasures'. Cannot insert duplicate key in object 'dbo.j5c_MasterMeasures'.
The statement has been terminated.

Here is my query:

insert into J5C_MasterMeasures (studentid, measuredate, measureid, RIT)

select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
join sysobjects so on so.name = 'J5C_Measures_Sys' 
join syscolumns sc on so.id = sc.id 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
where so.type = 'u' and sc.name = 'score_14' and a.score_14 is not null 
AND A.STUDENTID IS NOT NULL AND A.MEASUREDATE IS NOT NULL AND B.MEASURENAME IS NOT NULL
group by a.studentid, a.measuredate, B.measurename, B.LabelName, A.score_14
--HAVING COUNT(*) > 1

The strange thing is that if I run just the SELECT query (without the INSERT) and include the HAVING COUNT statement, it returns 0 records for > 1. So I don't know where the duplicate is coming from!

Answer 1

Is there data already in the J5C_MasterMeasures table? If so make sure that what you are inserting doesn't already exist

Answer 2

Use:

select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
  from [J5C_Measures_Sys] A
  join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
  join sysobjects so on so.name = 'J5C_Measures_Sys' 
  join syscolumns sc on so.id = sc.id 
  join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
 where so.type = 'u' and sc.name = 'score_14' and a.score_14 is not null 
   AND A.STUDENTID IS NOT NULL AND A.MEASUREDATE IS NOT NULL AND B.MEASURENAME IS NOT NULL
   AND NOT EXISTS(SELECT NULL
                    FROM J5C_MasterMeasures x
                   WHERE x.studentid = a.studentid
                     AND x.measuredate = a.measuredate
                     AND x.measureid = B.measurename +' '+ B.LabelName)
group by a.studentid, a.measuredate, B.measurename, B.LabelName, A.score_14

The NOT EXISTS will filter out already existing data.

Answer 3

Based on your earlier question, i believe that your primary key is A.studentid, A.measuredate, B.measurename. Please correct me if i am wrong on this.

Since you are grouping by two additional columns B.LabelName and A.score_14 in addition to your columns of your composite primary key, if there are any duplicates - which there can be provided they have different values of either B.LabelName or A.score_14 - you will violate your primary key constraint and this error will be thrown.

Your data will just not be unique enough to satisfy your primary key - which states that ONLY ONE ROW with a unique combination of A.studentid, A.measuredate, B.measurename can exist in your table

Answer 4

You should double-check your HAVING test. Make sure you are only including the column(s) that comprises the PK in your GROUP BY clause.