Duplicate records error during primary key creation! !

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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??

Error:-

'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!!

Posted 17-Sep-16 20:32pm

mousau

Updated 17-Sep-16 21:28pm

v2

Add a Solution

Comments

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.
Thanks
Regards

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2016-09-17T21:28:00

Solution 1

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:

SQL

ALTER TABLE EffectiveDate ADD PRIMARY KEY (ProdID, StartDate);

Posted 17-Sep-16 21:28pm

Wendelius

Comments

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??

Regards
Thanks

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.
Regards