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

views:

answers:

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.

+3 A:

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)......

marc_s 2009-12-24 07:35:57

are there any side effects of dropping primary key and then recreating it in production database.

Rohit 2009-12-24 08:29:40

you need to drop all foreign keys referencing that table's primary key first, and recreate them afterwards.

marc_s 2009-12-24 08:30:27

Use of artificial keys depends on the requirements of a particular table. Far too many people go into automatic mode and assign every table an artificial key without the slightest care as to the potential update anomalies it causes. They treat it like a _silver-bullet_ ... The _golden rule_ of development is that there are **NO silver-bullets!** PS: Using an artifical PK so "You wouldn't have to change it if yet another column comes along" is about the **worst** reason to use one. You mean to tell me you won't enforce uniqueness on the natural composite key?

Craig Young 2009-12-24 08:32:07

@Craig: well, in my experience, having a composite primary key is just **asking** for trouble down the road. It's messy to use (think referencing the table and having to always have five or six JOIN conditions......), it's messy if you ever need to change it. A single surrogate ID is much easier. To enforce Uniqueness, add a Unique index - if it's not the PK, it's much easier to maintain and change if needed.

marc_s 2009-12-24 09:31:23

@marc_s: "well, in my experience, having" _developers go on 'auto-pilot'_ "is just **asking** for trouble down the road". I won't argue the benefit of artificial keys - they _can_ be very useful. But if you have table A references table B, and table B uses an artificial key, then table A has no **meaningful context** of table B; and a **lot** of additional work is required in code and processing in order to achieve it. The point is that developers should understand the technique, know the pros and cons; and make their design decisions based on overall _current_ requirements.

Craig Young 2009-12-24 11:35:07

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.

Craig Young 2009-12-24 08:38:33

ansaurus

tags:

views:

answers:

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

related questions