CODEWITHSUNDEEP
sqlsql-serverdatabase
user3325454
user3325454

Reputation: 101

Cannot insert duplicate key row in object with unique index

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

Upvotes: 8

Views: 128372

Answers (4)

M.Ubeyd
M.Ubeyd

Reputation: 81

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

Upvotes: 0

Fado
Fado

Reputation: 55

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

Upvotes: 4

Colin
Colin

Reputation: 1836

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();

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107518

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.

Upvotes: 18

Related Questions