Howto: Change a table's PK and keep existing data?

Hi,

This is probably a database 101 question but...

What is the best way to modify a table's primary key while not losing all of the data?

I'm using Sql Server 2008. One of my tables currently has a PK field (bigint called 'pid') but I realized that this pid value can occur more than once in the table so I want make this field a simple bigint and add a new bigint field (called 'id') which would then become my new PK.


Thanks!

 

Hi,
Cool username you have there Jack.

I don't think there is any worry about lossing data if you are only changing the PK.
http://msdn.microsoft.com/en-us/library/ms189251.aspx

If you have SQL Server 2008 installed locally the visual designer is very easy to use for this.
Just make sure the data column you use for the PK does not allow NULL.
Salute,
Mark

 

Very cool. Worked like a charm. Thanks!