Duplicate key error for composite primary key when running update query without having duplicate

Question

I have a table with the below schema

    CREATE TABLE `my_field_table` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' ,
  `bundle` varchar(128) NOT NULL DEFAULT '',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `entity_id` int(10) unsigned NOT NULL,
  `language` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 49 (my_field)'

and the following two entries already present in the table.

+-------------------+----------+---------+-----------+----------+
|    entity_type    |  bundle  | deleted | entity_id | language |
+-------------------+----------+---------+-----------+----------+
| node              | article  |  0      | 9001      | 5555     |
| node              | article  |  0      | 9001      | abcc     |
+-------------------+----------+---------+-----------+----------+

When I run the following update query :

UPDATE `my_field_table` SET language='9002' WHERE entity_id = 9001;

I get the error message Error Code: 1062. Duplicate entry 'node-9001-0-9002' for key 'PRIMARY'

However I do not see such a combination of the field values already present which confuses me on the duplicate entry error. Usually I have at least one auto-increment key which I have as the primary key but this table does not have one and my lack of experience with DB isn't helping me either.

I tried searching for similar questions but the solutions suggested is to either check for existing entry or to create a new table with same structure and simulate this to check if my table is corrupted. The former does not fetch any results while I am able to reproduce the same error with the latter.

Some insight into the cause of this error would be greatly appreciated.

Answer 1

You have a primary key on the following fields, meaning that when combined they must be unique.

PRIMARY KEY (entity_type,entity_id,deleted,language)

Currently, they are unique because you have two entries in the table for language. After your update, language would have the same entry for both rows, and the rows would no longer be unique.

Answer 2

Your UPDATE statement will make the two rows you show identical. At that point, they will have the same primary key, so you can't change both of them successfully.