MySQL duplicate entry error even though there is no duplicate entry

Your code and schema are OK. You probably trying on previous version of table.

http://sqlfiddle.com/#!2/9dc64/1/0

Your table even has no UNIQUE, so that error is impossible on that table.

Backup data from that table, drop it and re-create.

Maybe you tried to run that CREATE TABLE IF NOT EXIST. It was not created, you have old version, but there was no error because of IF NOT EXIST.

You may run SQL like this to see current table structure:

DESCRIBE my_table;

---

Edit - added later:

Try to run this:

DROP TABLE `my_table`; --make backup - it deletes table

CREATE TABLE `my_table` (
  `number` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`number`,`name`),
  UNIQUE (`number`, `name`) --added unique on 2 rows
) ENGINE=MyISAM;

In case this helps anyone besides the OP, I had a similar problem using InnoDB.

For me, what was really going on was a foreign key constraint failure. I was referencing a foreign key that did not exist.

In other words, the error was completely off. The primary key was fine, and inserting the foreign key first fixed the problem. No idea why MySQL got this wrong suddenly.

I know this wasn't the problem in this case, but I had a similar issue of "Duplicate Entry" when creating a composite primary key:

ALTER TABLE table ADD PRIMARY KEY(fieldA,fieldB); 

The error was something like:

#1062 Duplicate entry 'valueA-valueB' for key 'PRIMARY'

So I searched:

select * from table where fieldA='valueA' and fieldB='valueB'

And the output showed just 1 row, no duplicate!

After some time I found out that if you have NULL values in these field you receive these errors. In the end the error message was kind of misleading me.

I had a similar issue, but in my case it turned out that I used case insensitive collation - utf8_general_ci.

Thus, when I tried to insert two strings which were different in a case-sensitive comparison, but the same in the case-insensitive one, MySQL fired the error and I couldn't understand what a problem, because I used a case-sensitive search.

The solution is to change the collation of a table, e.g. I used utf8_bin which is case-sensitive (or utf8_general_cs should be appropriate one too).