Avatar of sadaf_syed
sadaf_syed asked on

need to enter duplicate value in database for a unique identifier but getting error need work around

I am entering a value in our datbase that is a duplicate, its a company  thing. they used to do it here before but as im new im dont know the workaround for this.
im getting the error:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'aaaaaECOBODY_PK'. Cannot insert duplicate key in object 'ECOBODY'.
The statement has been terminated.
when I type the query:
update ecobody set RevTo='B' where PartNumber='5002265G' and ECONumber='6690'

need a workaround for that error so that this change can be made to the database.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
sadaf_syed

8/22/2022 - Mon
Aneesh

Hello sadaf_syed,

tthis is because i think the column RevTo is part of your  composite primary key, and there seems to be a row with the same values


GOD BLESS,

Aneesh R.
brad2575

Sounds like you need to change the primary key so this is not an issue on this constraint anymore (could cause some major issues though in the rest of your program as this was probably done for a reason).

Maybe add an auto increment field and just make that the primary key (this WILL slow it down though if you are referencing the keys on this field that are already the PK).
chapmandew

Consider what you are doing....the PK is most likely there for a reason...why do you want to enter a dupe?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
sadaf_syed

well i dont want to the ppl that use this database say it is common practice, and the dba has left the compnay so im stuck trying to change this, this is just the way they Rev some of their parts.
speedracer10

A primary key in a table exists for a reason, they are enforced such that you CAN NOT have duplicate values for a primary key (Col ECONumber). My advice would be to edit the table and have this column auto-increment.
(then you will not have to input a value to ECONUMBER.)

What you are trying to do make the presence of a primary key redundant.

The only way this would even be possible is if you drop the primary key constraint.
(ALTER TABLE ecobody DROP CONSTRAINT aaaaaECOBODY_PK)
ASKER
sadaf_syed

Rev To is also a primary key this is one case where i need to remove that restriction. how do i do that?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
speedracer10

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
sadaf_syed

thanx speedracer, it just a company thing, i dont call the shots. but another question:
ok and once we make the change if we want to place the primary key restraint again how would we do that?
ASKER
sadaf_syed

how do i add the constraint back once im done making my change?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
sadaf_syed

no the previous dba let them have duplicate entrie. he would drop the constraint and add it, i have 408 records that have been altered this way, so its a practice i can not change.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
chapmandew

If there are duplicates, then you can't add a unqiue constraint or a PK back to the table on the set of field(s) that have the duplicate values.
Mark Wills

You can add constraints back with NOCHECK, but then that is not the primary key with must be unique by definition - think there must be an identity field floating around somewhere... and heaven help if there is ever any reason to data to be checked in the future - will be a nightmare...

adding a primary key constraint with NOCHECK does not work... in sql 2005 and sql 2008 at least - what version are you on ?
ASKER
sadaf_syed

hahaa we are using microsoft sql 2000 so we are really behind... neway i found a strange work around there is another table that can change revto details and its data is pulled in here, the user was right about the practice of changing revs but didnt really know how the change took place. thans for all ur help.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.