Corresponding columns in the foreign key and the referenced key must have similar data types. I quote one precondition of adding a foreign key constraint from MySQL 8.0 Reference Manual below to sustain my point. But how different is "different"? how same is "same"? Sometimes, they should be exactly the same even in every tiny specification. Same data type of two columns is good to go. Column Definition Mismatchīasically, we can't build referential relationship between two different data types of columns. If you still got "ERROR 1215 (HY000): Cannot add foreign key constraint", you can go on reading this post because things are getting complicated. mysql> alter table customers add index (email) mysql> alter table customers add index customers_email_idx (email) For example, adding an index with an explicit name. The cure is pretty easy, just add indexes on columns that are required to have. So please make sure that you have indexes on the parent and child columns before adding any foreign key constraints. One obvious mistake is that you do not have indexes both on the referenced and referencing columns. We have to find out the root cause by ourselves. It only shows that it failed to create a foreign key constraint, but I don't see any hints or useful information in it. Maybe the message of ERROR 1215 (HY000) is somewhat shallow. ERROR 1215 (HY000) Cannot add foreign key constraint Rationale and Solutions for Cannot add foreign key constraint
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |