AMartinNo1 AMartinNo1 - 5 months ago 13
MySQL Question

Duplicate entry [...] for key 'PRIMARY'

I am facing a weird behaviour regarding updating the table

oxseo
in an oxid eshop 4.7.

The table
oxseo
has following schema:

+------------+---------+----------+----------+----------+----------+-------+
| OXOBJECTID | OXIDENT | OXSHOPID | OXLANGID | OXSTDURL | OXSEOURL | [...] |
+------------+---------+----------+----------+----------+----------+-------+


and has a PRIMARY (that's also the keyname) key on the fields:

+---------+----------+--------+
| OXIDENT | OXSHOPID | OXLANG |
+---------+----------+--------+


and I try to execute following sql (replaced real values with dummy data) via the linux shell:

UPDATE oxseo
SET
OXIDENT = "8e4b0ac7[...]",
OXSEOURL = "my/seo/url/"
WHERE
OXOBJECTID = "123"`


which leads to the error:


ERROR 1062 (23000): Duplicate entry '8e4b0ac7[...]-oxbaseshop-0' for key 'PRIMARY'`


However, when looking for the string
8e4b0ac7[...]
in the whole table using the phpMyAdmin there is no row returned. Despite that I have checked manually using

SELECT * FROM field1 = hash
SELECT * FROM field2 = hash


and so on.

Anyone has an idea what the cause could be?

Answer

It is very simple. Whatever that update statement is attempting to do would leave the table in a state with duplicate entries (2 or more rows) in the PRIMARY key as seen in the output from show create table oxseo.

That PRIMARY key may be on a single column, or it may be a composite (multi-column) key.

The db engine forbids it, because the schema forbids it.

Comments