sql help with association table update

Question

update: This is more of a maintenance issue than the normal case.

I have the following table:

CourseId   StudentId
---------------------
1          1
1          2
2          1

CourseId and StudentId are composite primary key.

Let's say I wanted to update where courseid=1 to courseid=2 for some reason. This will cause a primary key constraint issue.

What are some different ways you could solved this?

Answer 1

http://www.1keydata.com/sql/sql-primary-key.html

Answer 2

In general, you should try to choose columns for your primary keys that never change, because changing them can be a pain for many reasons, one of which is a primary key constraint violation.

Why are you changing the CourseId (which is probably a PK in a Course table), and why are you changing it to a CourseId which already exists?

EDIT: Adding an answer

The problem with the accepted solution is that dropping and re-creating a clustered index (primary key) can be very time-consuming and problematic as explained here if you're dealing with tables of significant size.

A better solution would be to not insert duplicates in the first place. For example, if these are the only two columns in the table, you can simply update the records which don't cause a conflict, and then delete the ones that which will (because rows already exist with the set of values you want).

begin tran

create table #example (CourseId int, StudentId int)

insert into #example values (1,1), (1,2), (2,1)

declare @OldValue int, @NewValue int
set @OldValue = 1
set @NewValue = 2

-- Only update records which won't cause a conflict    
update e
set CourseId = @NewValue
from #example e 
left join #example e2 
on (e.StudentId = e2.StudentId and e2.CourseId = @NewValue)
where e.CourseId = @OldValue 
and e2.StudentId IS NULL

-- Delete the records which weren't updated
delete from #example 
where CourseId = @OldValue

select * from #example

rollback

Answer 3

1. Disable/Drop the primary key constraint

2. Run the UPDATE statement:

   UPDATE YOUR_TABLE
      SET courseid = 2
    WHERE courseid = 1
   

3. Remove duplicates - this solution assumes SQL Server 2005+:

   WITH duplicates AS (
       SELECT t.courseid,
              t.studentid,
              ROW_NUMBER() OVER(PARTITION BY t.courseid, t.studentid) AS rank
         FROM YOUR_TABLE t)
   DELETE FROM duplicates
    WHERE rank >= 2
   

4. Enable/recreate the primary key

Answer 4

The best answer is really going to depend on the purpose of your table and everything.

If the update you mention is a plausible thing you'd have to do, I'd suggest an IDENTITY primary key separate from those two, and dropping the contraint on them.

Answer 5

I'm dittoing Ryan. Your question implies poor design (or poor understanding). This does not mean that you are stupid!

Your PK, composite or otherwise, should be the smallest number of columns (i.e. pieces of data) that can uniquely identify a single object. Perhaps adding a column such as IsActive or RegisteredDate to the PK would alleviate any conditions in which you would "need" to update either ID column.

Also, because I've done a fair amount of data design, I can infer from the fact that these two ID columns make up the PK that this is a "relationship table" and both are foreign key to another table.

My suggestion would be to not change either ID.