Click here to Skip to main content
15,901,505 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to add primary key to newly added column in existing table name Product_Details.

Table name : Product_Details

New Column added: Product_Detail_ID (int and not null)

I am trying add primary key to "Product_Detail_ID". (Please note: There are no other primary or foreign key assigned to this table)

I am trying with this query but getting error.

ALTER TABLE Product_Details

add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)


The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Product_Details' and the index name 'pk_Product_Detils'. The duplicate key value is (0).
Am I missing something here? I am using SQL2008r2. I would appreciate any help.

[edit]code blocks fixed[/edit]
Updated 17-Dec-12 12:28pm

To create a new column would be:
T-SQL SQL Server 2008 R2 will create something like this:
ALTER TABLE Product_Details
ADD Product_Detail_ID INT NOT NULL
--additional cells can be added if needed

--Followed by Clustered or non-clustered depending on need. in addition to
--additional indexing if needed

And it shounds like you already have a Primary key that you can remove with this:

ALTER TABLE Product_Details
DROP CONSTRAINT pk_Production_Details

You can right click on that table and click on "Script Table AS" and do
"ALTER" and it will give you a basis to work off of.

If you are doubting the effectiveness of that above transaction working you can test it by adding this BEFORE your CODE:

and to complete that transaction do highlight the following text and execute:

Or undo that transaction highlight the following text and execute:

That should give you some better understanding of how to work with what your needed to do and be more confident that the changes your making can be undone to some extent.

Hope that helps.
Share this answer
Since you already have an entries inside the table and trying to add a new column of type int with default value 0 will add as many entries for each rows for this column.

you have 100 records in table having two columns. Now trying to add a new column will try to update the 100 entries for this column with its default value in your case it was set as zero.since the column you are going to add is marked as Primary Key it wont allow you to generate same default value zero to all the 100 entries.

I would suggest add the column without primary key and fill unique data to this columns say 1-100 and add primary key to this field so that you wont loose any existing data.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900