CODEWITHSUNDEEP
sqlsql-server-2005
Rohit Raghuvansi
Rohit Raghuvansi

Reputation: 2864

Alter composite key to include newly added column in sql server 2005

I have a table GB_Assignor_Assignee. I have a primary key which includes this combination(StateCode, CountyID, Doc_Type_Group_Code). Now i have to add a new column Doc_Type_Code. I added it by altering table. I want to include this new column inside this primary key.So my combination will be(StateCode, CountyID, Doc_Type_Group_Code,Doc_Type_Code).

How can i alter this primary key to add new column. I donot want to drop it and then recreate it. Please suggest.

Upvotes: 0

Views: 889

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

You have to drop and recreate your PK.

  • This involves dropping any foreign keys that reference it. This should be obvious in any case as the foreign keys would also have to change to reflect the new column. (Hopefully not many in the case of composite PKs).
  • Drop the PK itself.
  • Create the new PK with the additional column.
  • Recreate all foreign keys.

The easiest way to do this is to make the change in SQL Server's table designer, and ask it to generate the change script for you.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

If you want to change the primary key to include a new column, you have to drop and recreate it - there's no other way. You cannot add a column to an existing primary key after it's been created.

The question is: wouldn't you be better off creating a new artificial ID (of type INT) as your PK? You wouldn't have to change it if yet another column comes along, referencing the table will be MUCH easier (JOIN on just a single INT instead of five or six columns)......

Upvotes: 3

Related Questions