Adding column with primary key in existing table

Question

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

Error:

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.

Solution 1

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
GO
ALTER TABLE Product_Details ADD Product_Detail_ID int identity(1,1) not null
GO
ALTER TABLE Product_Details
add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)
GO

Solution 2

In mysql, I was able to achieve with following query

ALTER TABLE table_name ADD new_column int NOT NULL AUTO_INCREMENT primary key

Solution 3

Add Primary Key to First Position

ALTER TABLE table_name 
ADD column_name INT PRIMARY KEY AUTO_INCREMENT FIRST;

Reference: Stack Overflow | Tech On The Net

Solution 4

You are getting the error because you have existing data that does not fullfill the constraint.

There are 2 ways to fix it:

• clean up the existing data before adding the constraint
• add the constraint with the "WITH NOCHECK" option, this will stop sql server checking existing data, only new data will be checked

View more solutions

Comments

[Eric Petroelje]

I would add that using WITH NOCHECK is almost always a bad idea :)

[Joy1979]

Thanks for your help. This query also worked for me but I inserted data manually (edit 200) and then executes with my query..It worked!!

[EWit]

What does this answer do different then the already provided answers?

[Shahram Banazadeh]

CAUTION:This will delete all data in this column

[Russell Munro]

For SQL Server use IDENTITY not AUTO_INCREMENT

[Deepam Gupta]

@EricPetroelje I am curious to know why WITH NOCHECK is not always a good idea? 🤔