×

Welcome to TagMyCode

Please login or create account to add a snippet.
1
0
 
0
Language: SQL
Posted by: Massimo Zappino
Added: Oct 12, 2011 12:10 PM
Modified: Jun 27, 2012 9:35 PM
Views: 297
If you encounter the error 1005 you probably have someting wrong in your tables.

You should check some things:
* Table Engine value must be the same for both tables, for example InnoDB
* Columns type must be the same for the referenced column, for example INT(11) UNSIGNED
* Both column must be indexed, for example PRIMARY KEY, INDEX, UNIQUE
  1. /*
  2. You can execute the MySQL command to see the status of InnoDB engine:
  3. */
  4. SHOW ENGINE INNODB STATUS;
  5.  
  6. /*
  7. For example in this example you can see the last foreign key error where missing index in the referenced column.
  8.  
  9.  
  10. ...
  11.  
  12. ------------------------
  13. LATEST FOREIGN KEY ERROR
  14. ------------------------
  15.  
  16. ...
  17.  
  18. Cannot find an index in the referenced table where the
  19. referenced columns appear as the first columns, or column types
  20. in the table and the referenced table do not match for constraint.
  21. Note that the internal storage type of ENUM and SET changed in
  22. tables created with >= InnoDB-4.1.12, and such columns in old tables
  23. cannot be referenced by such columns in new tables.
  24. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
  25. for correct foreign key definition.
  26. */
  27.