sqlsql-serversql-updateshared-primary-key

SQL Update: Cannot change one of composite key value of first record

In MSSQL Server, I have a table StudentCourse with a Composite Primary Key (StudentID, CourseID). I am trying to change the selected student to another course. One student record of each course group is preventing me to do UPDATE operation.

StudentID CourseID

   1          1
   1          2
   1          3
   2          2
   2          3
   2          4

I can update (1, 2), (1, 3) records' CourseID to 5, but I can't update (1, 1) record's CourseID to 5. Similary, I can update (2, 2), (2, 3) records' CourseID to 5, but I can't update (2,4) record's CourseID to 5.

Only one record of such CourseID group is preventing me to change its CourseID field. I am getting the following error.

Violation of PRIMARY KEY constraint 'PK_StudentCourse'. Cannot insert duplicate key in object 'StudentCourse'. The statement has been terminated.

I don't know it is first or last record of each group prohibits me to change CourseID. I am sure there is no record with CourseID = 5 in StudentCourse table, and I have a course record with CourseID of 5 in Course table.

Any help would be appreciated.

Solution

  • I found the problem. When I was building Conditions for query string, one condition wasn't adding GroupID criteria. The query string happens to miss that GroupID crieria when that record was included in query string. It was happening as follow.

    UPDATE StudentCourse SET CourseID = 5 WHERE CourseID = 1 AND StudentID IN(2,3)
UPDATE StudentCourse SET CourseID = 5 WHERE StudentID IN(1,2,3)

UPDATE StudentCourse SET CourseID = 6 WHERE CourseID = 2 AND StudentID IN(2,3)
UPDATE StudentCourse SET CourseID = 6 WHERE StudentID IN(2,3,4)

    Of course, my query was violating primary key rule without CourseID criteria. Thanks for your time, mates.