1

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.

  • Welcome to DBA.SE! Once your question's been answered, please remember to mark one of the answers as the one that helped you the most. This lets others looking at the post know that an answer was found; otherwise, even with answers posted, others may think that they didn't resolve your question, and keep trying to help find an answer. – RDFozz Jul 28 '17 at 16:50
  • @RDFozz thanks, was waiting for the accept answer time restriction to complete – optimusprime619 Jul 28 '17 at 16:51
  • 1
    This will sound odd, perhaps - but would you consider changing the accepted answer to Chad's? It's a more detailed answer, and I only beat him by a couple of minutes – RDFozz Jul 28 '17 at 18:32
  • Appreciate that, @RDFozz :) – Chad Cahill Jul 31 '17 at 19:55
4

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.

3

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.

  • ah, that makes sense.. so my update would try to set the same primary key on both the existing records and so that would cause the integrity constraint, instead the error is returned! feel rather silly that I didn't think of this before posting.. thank you! – optimusprime619 Jul 28 '17 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.