Cannot insert duplicate key row in object with unique index

Question

I am trying to run the following query:

update OAR.oa_AcademicHead
set  personid=15857
where personid=1234

but I am getting the error:

Cannot insert duplicate key row in object 'OAR.oa_AcademicHead' with unique index 'IX_oa_AcademicHead_personid'.
The statement has been terminated.

What can be done to fix this??

why in the world would you want to change the primary id of a row. isn't it like, against any database integrity principle? — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Mar 5, 2014 at 21:35
Maybe you just want to delete 1234? I'm rather fascinated by this idea of people merging into each other. — Gordon Linoff
– Gordon Linoff, Commented Mar 5, 2014 at 22:32

Cᴏʀʏ · Accepted Answer · 2014-03-05 21:39:08Z

18

A row where personid is 15857 already exists in the table. The unique index on that column is preventing you from committing another record with the same personid (an update is really a delete and insert).

You'll have to remove the existing person with that id first before running your query.*

_{* Disclaimer: make sure this is actually what you want to do; be aware of the issues it might cause.}

edited Mar 5, 2014 at 21:39

answered Mar 5, 2014 at 21:33

Cᴏʀʏ

108k20 gold badges169 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3325454 Over a year ago

There is only one row with the personid 15857 in the given table and personid is foreign to key to some other table and primary key of the table is academicheadid. Why can't we have two rows with the same person id. Also, I cannot delete the person id 1234 because he is academic of some other organization also. So, if I delete 1234 the information regarding is AH status will be lost. What else can be done to fix it? Thanks.

Cᴏʀʏ Over a year ago

The index IX_oa_AcademicHead_personid is specified as being a unique index, meaning every personid in the table must be unique; there can be no duplicates. That "some other table" from which it is a foreign key sounds like a "Person" table. In reality, two different academic heads cannot be the same person, hence the unique index. Without seeing the entire database schema it would be hard to suggest a course of action.

wibeasley · Accepted Answer · 2021-08-27 02:38:28Z

4

I am aware that this question is really old. But still might be helpful.

I've experienced this problem with my database and managed to fix it after a few hours of struggling.

My database has an Attachments table, which has primary key ID. I also have messageID column and was not able to create two rows with same messageID. I have found that messageID is indexer. I don't know what are indexers used for exactly, but I simply deleted indexer and now it works fine.

picture of my database and where I found indexer

edited Aug 27, 2021 at 2:38

wibeasley

5,3073 gold badges38 silver badges68 bronze badges

answered Aug 25, 2021 at 14:13

Fado

555 bronze badges

2 Comments

RPD Aug 19 at 15:53

I know this comment is really old on a question that is even older, but I need to say - THIS IS TERRIBLE ADVICE! The index is there for a reason, and that reason is to enforce uniqueness. When you remove the index you remove the requirement for uniqueness. This is like throwing away your fire alarm because it keeps waking you up when there's fires. The index was literally doing the exact job it was put in place for, and now you probably have a table full of nonsense duplicates.

Goronmon Aug 22 at 19:13

Agreed. Deleting an index without having any idea what they are is a terrible idea.

Colin · Accepted Answer · 2017-05-29 18:00:16Z

0

If you get said error while using ASP.NET MVC do something like this:

db.Entry(personObject).State = EntityState.Modified;
db.Entry(personObject).Property(x => x.personid).IsModified = false;
db.SaveChanges();

answered May 29, 2017 at 18:00

Colin

1,8561 gold badge21 silver badges25 bronze badges

Comments

M.Ubeyd · Accepted Answer · 2021-12-17 12:29:55Z

0

If your model is decorated with an indexer like [Index(nameof(SomeForeignKey), nameof(SomeOtherForeignKey), IsUnique = true)], then you will counter problems like those, like "An error occurred while updating the entries. See the inner exception for details."

answered Dec 17, 2021 at 12:29

M.Ubeyd

811 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Cannot insert duplicate key row in object with unique index

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related