Alter table syntax to change primary key

[batteam]

I need to remove a primary key from one column and add it to another on an existing table.

I created the table with the following:
Create table xxxxxx

xxx
xxx

contstraint pk_status_log primary key (client_data_id, account_id)
go


Now I need the primary key removed from account_id and added to a column named 'status_number'.

Can anyone help me with the correct 'Alter table' syntax? Thanks for any help you can provide.

 

[gmmastros]

Sounds to me like you want to do this:

Alter Table XXX Drop Constraint pk_status_log
GO
Alter Table XXX Add Constraint pk_status_log Primary Key Clustered (client_data_id, Status_Number)

I don't know what the performance implication of this will be on large tables. If your table is large, it could take quite a while for the last query to run. Also, you should make sure the the Status_Number column does not allow NULLS and that there are no nulls in any of the rows.

Also... please make sure you have a good backup before you run this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[batteam]

thanks for the help. I was having trouble with the drop part. Now is see that dropping the constraint removes all the existing primary keys.

Actually, my table was just created and we're in the development stage so there's only a few rows in it. Thanks again.