Mysql – Duplicate entry error for unique key that doesn’t exist

MySQLmysql-workbench

I have a model that had the following fields:

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
| CCC      | ***      |
+----------+----------+

But then I deleted the last row (CCC) and tried to insert a new one with the same username:

INSERT INTO users (username, password) SELECT 'CCC', '***'

But I got this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'CCC' for key 'username_UNIQUE'

And if I query for all the existent rows, I get only two rows:

SELECT * FROM users

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
+----------+----------+

I suspect this has to do with the data not syncing to disk, since in the first days, when I first created the model using MySQL Workbench, it wouldn't appear on Adminer only until after a reboot, but I have no idea as to how to force a sync on the data to disk, or even if it's the issue.

I'm using MariaDB 5.5.39 on Fedora 20 64 bit on linux 3.17.4-200 as the DBMS and InnoDB as the DB storage engine.

Update:

It looks like this happens with all and every username I try, so 'askaksjk' will also trigger the same error, but if I insert a new row without using a SELECT statement, it adds the record correctly:

INSERT INTO users (username, password) VALUES ('CCC', '***')

Does it has anything to do with the fact that the SELECT already returns a record with the same username as the one I'm trying to add?

Best Answer

Your update is the correct way to insert data. Using INSERT INTO ... SELECT ... requires a source table and is not meant to be used with static values.

Using INSERT INTO ... VALUES ... is for inserting static data, such as CCC, into the table. The two are not interchangable and will cause the issues you've been seeing.

You can check the documentation on INSERT INTO ... SELECT ... here and note that it only talks about using a table source.

Related Question