MySQL 5.5 foreign key constraint fails when foreign key exists

This does appear to be a bug introduced since MySQL 5.5.9 on Mac OS X: http://bugs.mysql.com/bug.php?id=60309

It is marked as fixed in 5.5.13 (released May 31) and mentioned in the release notes: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html

Alternatively, there is a workaround listed in the bug report that I've verified on 5.5.10 and reproduced below:

[20 Mar 11:29] Harald Neiss

I also received a new MBP and reinstalled MySQL (mysql-5.5.10-osx10.6-x86_64). Finally I
came across the same problem as described above. So here is the query result and what I
did to solve it.

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

Dropped database, created the file /etc/my.cnf with the following content:

[mysqld]
lower_case_table_names=1

Restarted the MySQL daemon and repeated the query:

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

I recreated the tables and everything works fine.

Not surprising IMHO. I have found numerous bugs in MySQL. For example running queries with where clauses such as "WHERE some_tinyint_column = 0" would produce no data when it should, but rewriting the clause as "WHERE (NOT some_tinyint_column = 1)" produces results. After some research I found that it was a bug that was supposed to have been fixed, but in the release I was using, the bug was still there.

Conclusion: when something makes absolutely no sense in MySQL, I usually find it safe to assume it's a bug and start researching for info along those lines.

I was also facing the same issue and did not find any solution which solves my issue. So this issue can occur due to many reasons. I am just trying to put as many of these reasons and solutions at one place and also putting the fix which helped me to resolve this. I hope this will help someone in future.

1) Provided by Penfold - case sensitivity of table names
2) Engine mismatch in Parent and Child Table
3) Charset mismatch in Parent and Child Table
4) Parent(Id) and Child(Patent_Id : both must have exactly same data types (Also Signed/Unsigned)
5) InnoDB lost table but table exists - Please find solution here

SQL Query (show engine InnoDB status) gives this error (its .ibd file does not currently exist!) Blockquote

6) Few older versions of mysql has this bug.Bug#60196, Bug#60309
7) This works for me (if everything above seems fine, you probably needs this)- Dropping the foreign key from Child Table and Add Constraint again. If it also fails then drop parent table and create it again, before this you need to drop foreign key from all the Child Tables. This one is last possible thing.
Further Read :
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names