0

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.

Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158
Jonas T
  • 2,989
  • 4
  • 32
  • 43
  • What does your update statement that gives the error look like? – Mikael Eriksson May 01 '11 at 12:25
  • can you try running `SELECT CourseID From StudentCourse WHERE CourseID = 5` and verify that there are indeed no such records? – Menahem May 01 '11 at 12:37
  • Can you update the question to include how the script for your index? Also, I'm assuming that you don't want to allow duplicates in this table (meaning no student should be able to sign up for the same course twice). Is that correct? – rsbarro May 01 '11 at 19:02
  • @Mikal, two failed UPDATE statements are "UPDATE StudentCourse SET CourseID = 5 WHERE CourseID = 1 AND StudentID IN(1)" and "UPDATE StudentCourse SET CourseID = 5 WHERE CourseID = 2 AND StudentID IN(4)" All other UPDATE statements work such as "UPDATE StudentCourse SET CourseID = 5 WHERE CourseID = 1 AND StudentID IN(2,3)", "UPDATE StudentCourse SET CourseID = 5 WHERE CourseID = 2 AND StudentID IN(2,3)" – Jonas T May 01 '11 at 21:48
  • @Menahem, That SELECT CourseID=5 returns zero row. @rsbarror. @rsbarro, that's correct. There shouldn't be two same StudentIDs with two same CourseIDs records in the table. – Jonas T May 01 '11 at 21:51
  • @Jonas Is it possible that the index was created WITH NOCHECK? That would allow the index to be created but also for conflicting data to be present. Also, can you generate a SQL script for the index and add it to the question? – rsbarro May 01 '11 at 22:45
  • @rsbarro, when I query using this statement "select * from dbo.sysindexes where object_name(id)='StudentCourse'" It returns, the two rows name=>PK_StudentCourse, _WA_Sys_CourseID_3C1FE2D6 – Jonas T May 01 '11 at 23:24
  • @rsbarrow, I just checked FK Relationship and both of the check boxes of "Check existing data on creation" are off. – Jonas T May 01 '11 at 23:44

2 Answers2

3

The error you are seeing means that you are trying to create a record with the same value of primary key as another existing record. You are making a mistake here, but you are not giving enough information, to understand what your mistake is.

When I have a problem, I find it useful to create a small repro, that can illustrate the problem, so that I can show it to other users. Sometimes, when I try to create a simple repro, the repro actually works without a problem. This lets me know that there is something different in this working "repro" and my problem case. Next step for me would be to bridge the gap between them, modify either of them to make them closer until the difference in behaviour disappears. The step that made it, usually reveals the culprit of the behaviour being investigated.

In your case I can make following simple steps, to prove, that SQL Server is operating as expected:

I create a table:

CREATE TABLE [dbo].[StudentCourse](
    [StudentID] [int] NOT NULL,
    [CourseID] [int] NOT NULL,
 CONSTRAINT [PK_StudentCourse] PRIMARY KEY CLUSTERED 
(
    [StudentID] ASC,
    [CourseID] ASC
))

I add test data in:

INSERT INTO [dbo].[StudentCourse] values (1,1)
INSERT INTO [dbo].[StudentCourse] values (1,2)
INSERT INTO [dbo].[StudentCourse] values (1,3)
INSERT INTO [dbo].[StudentCourse] values (2,2)
INSERT INTO [dbo].[StudentCourse] values (2,3)
INSERT INTO [dbo].[StudentCourse] values (2,4)

I perform the updates you have described:

UPDATE [dbo].[StudentCourse] SET CourseID = 5 where StudentId = 2 and CourseID = 4
UPDATE [dbo].[StudentCourse] SET CourseID = 5 where StudentId = 1 and CourseID = 1

I can see that these work just as they should.

Try to understand what you are doing differently, and you'll find the cause of your problem.

Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158
  • I have tried that too in another database and it worked too but I can't just drop the table and create again. I can backup data but it will break everything, relationships, indexing etc. I think there could be problem with some rules in that table. – Jonas T May 01 '11 at 21:53
1

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.

Jonas T
  • 2,989
  • 4
  • 32
  • 43