Click here to Skip to main content
15,179,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All
I have a table where there records are unique, however they might be present multiple times, due to changes associated with their attributes. Accordingly I have ProdID and StartDate col, there had been changes made in the price of the products.But while making that prodId as primary key, SQl Server throws error!!Then how do i resolve this??

'EffectiveDate' table
- Unable to create index 'PK_EffectiveDate'.  
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.EffectiveDate' and the index name 'PK_EffectiveDate'. The duplicate key value is (PCOS103).
Could not create constraint. See previous errors.
The statement has been terminated.

What I have tried:

I am still unsure, but then how some of the similar tables are stored with certain records as primary keys in certain databases?Or Am I missing something!!
Updated 17-Sep-16 22:28pm
Wendelius 18-Sep-16 3:05am
What is the statement which you try to use to create the constraint?
mousau 18-Sep-16 3:16am
Hi Mika
Thanks a lot for the response.I did not use the create table statement,I have already a similar table created in another DB, I just wanted to replicate on to my SQL server, I used the graphical method of table creation, right click--->Design. In such situations is it advisable that i do use the create table statement( with constraint option)???
Then Let me try and get back again on this.

1 solution

Based on the error there are two options: Either you really have duplicate data or you haven't defined correct columns for the constraint.

If the idea is that a single ProdID can exist several times but not for the exact same StartDate then ensure that you create a key which contains both columns.An SQL statement could be something like:
ALTER TABLE EffectiveDate ADD PRIMARY KEY (ProdID, StartDate);
mousau 18-Sep-16 3:49am
Hi Mika
One thing still confused me, the record(PCOS103)is present twice, with following details:-
PCOS103 NeemFaceWash AyurvedicCosmectics 2 250 2016-02-06
PCOS103 NeemFaceWash AyurvedicCosmectics 2 260 2016-09-18

1)As you can see the date column data is not duplicated, then why did the error rise?
2)One more doubt, I was trying to make both those cols: ProdID and StartDate primary keys , but graphically, not by query, and it ended up with errors, however the command worked fine????Any reasons??

Wendelius 18-Sep-16 4:07am
Just guessing here but you probably tried to make the columns as separate keys. The thing is that both of the columns need to be inside a single key, in other words, you need a composite key.

Have a look at Compound key - Wikipedia, the free encyclopedia[^]
mousau 18-Sep-16 4:24am
Hi Mika
Thanks a lot for your answers, perhaps lots of flexibility to handle tables lies in the commands/codings rather than graphical usage.

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