MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

by

When you get this vague error message, you can find out the more specific error by running 

SHOW ENGINE INNODB STATUS;

The most common reasons are that when creating a foreign key, both the referenced field and the foreign key field need to match:

  • Engine should be the same e.g. InnoDB
  • Datatype should be the same, and with same length.
    e.g. VARCHAR(20) or INT(10) UNSIGNED
  • Collation should be the same. e.g. utf8
  • Unique – Foreign key should refer to field that is unique (usually private) in the reference table.

Another cause of this error is:
You have defined a SET NULL condition though some of the columns are defined as NOT NULL.

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. How to select the nth row in a SQL database table?
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. Error Code: 2013. Lost connection to MySQL server during query
  5. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  6. Storing JSON in database vs. having a new column for each key
  7. How do I restore a dump file from mysqldump?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. Adding a leading zero to some values in column in MySQL
  11. View’s SELECT contains a subquery in the FROM clause
  12. Check for x consecutive days – given timestamps in database
  13. MySQL Insert Into from one Database in another
  14. How do I get the ID of multiple inserted rows in MySQL?
  15. How to insert data to MySQL with auto-incremented column(field)?
  16. Does mysql have the equivalent of Oracle’s “analytic functions”?
  17. Case statement in MySQL
  18. MySQL: LAST_INSERT_ID() returns 0
  19. Remove special characters from a database field
  20. Using reserved words in column names
  21. MySQL query, MAX() + GROUP BY
  22. Calculating a Moving Average MySQL?
  23. MySQL – Replace Character in Columns
  24. Strings as Primary Keys in MYSQL Database [closed]
  25. FIND_IN_SET with multiple value [duplicate]
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. What is the difference between SQL and MySQL? [closed]
  28. How to use a user variables in MySQL LIKE clause?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment