Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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)
 
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 SQL2008r2. I would appreciate any help.
 
[edit]code blocks fixed[/edit]
Posted 17-Dec-12 11:23am
Joy1979316
Edited 17-Dec-12 13:28pm
Nelek59.9K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
 
CONSTRAINT [PK_Product_Detail_ID] PRIMARY KEY
--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:
BEGIN TRAN
and to complete that transaction do highlight the following text and execute:
COMMIT
Or undo that transaction highlight the following text and execute:
ROLLBACK
 
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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
 
say:
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 479
2 Maciej Los 305
3 Mathew Soji 195
4 Afzaal Ahmad Zeeshan 170
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,777
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100