Adding a column with a primary key in the existing table


I am trying to add primary key to newly added column in existing 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 SQL Server 2008 R2. I would appreciate any help.

If you want SQL Server to automatically provide values for the new column, make it an identity.

ALTER TABLE Product_Details DROP COLUMN Product_Detail_ID
ALTER TABLE Product_Details ADD Product_Detail_ID int identity(1,1) not null
ALTER TABLE Product_Details
add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)