[Solved] How to solve MySQL error code: 1215 cannot add foreign key constraint?

Error Message:


Error Code: 1215. Cannot add foreign key constraint

Example:


Error Code: 1215. Cannot add foreign key constraint

 

Possible Reason:

Case 1: MySQL storage engine.

MySQL supports several storage engines, comparing features of the different mysql storage engines are given below. Note that only InnoDB storage engine supports foreign key, when you are using different mysql storage engine you may get the error code: 1215 cannot add foreign key constraint.

MySQL STORAGE ENGINE FEATURES

Case 2: Key does not exist in the parent table.

When you are trying to reference a key on the parent table which is not exist, you may likely get the error code: 1215 cannot add foreign key constraint. When you are trying to reference a key on parent table which is not a candidate key (either a primary key or a unique key) you may get the error code: 1215 cannot add foreign key constraint. According to definition a foreign key must reference a candidate key of some table. It does not necessarily to be a primary key. MySQL requires index on corresponding referenced key, so you need a unique key.

Case 3: Foreign key definition.


When the definition of the foreign key is different from the reference key, you may get the error code: 1215 cannot add foreign key constraint. The size and sign of the integer must be the same. The character string columns, the character set and collation must be the same. Otherwise you may get the error code: 1215 cannot add foreign key constraint. The length of the string types need not be the same.

Case 4: Foreign key as a primary key.

When you are using composite primary key or implementing one-to-one relationships you may using foreign key as a primary key in your child table. In that case, definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL, defining the referential action in such a way may produce the error code: 1215 cannot add foreign key constraint.

Case 5: Referential action – SET NULL.

When you specify SET NULL action and you defined the columns in the child table as NOT NULL, you may get the error code: 1215 cannot add foreign key constraint.

Solution:







Case 1: Storage Engine.

Only MySQL storage engine InnoDB supports foreign key, make sure you are using InnoDB storage engine. You can use the following command to determine which storage engine your server supports.

mysql > SHOW ENGINES \G

To determine the storage engine used in the corresponding table, you can run the following command:

mysql > SHOW CREATE TABLE table_name;

MySQL allows you to define storage engine on table level, you can assign the storage engine by using the following statement:

mysql > CREATE TABLE table_name (id INT) ENGINE = INNODB;

Example:

CREATE TABLE student (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

To alter the storage engine of an existing table, you can run the following statement:

mysql > ALTER TABLE table_name ENGINE = INNODB;

Case 2: Key does not exist in the parent table.

Make sure your parent table contains at least one key to which you are going to create a reference key.
You can use the following statement to check the definition of a table:


mysql > SHOW CREATE TABLE table_name;


If the key does not present in the table, you can create a new key by using following statement:
If your table does not have unique column, create a new unique data field and set it as unique so that you can have your existing data by using the following statement:


mysql > ALTER TABLE table_name ADD Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;


If the existing table contains the unique data field you can assign it as unique key, by using following statement:


mysql > ALTER TABLE table_name ADD CONSTRAINT constr_ID UNIQUE (column_name);


Case 3: Foreign key definition.

The data type must be same for both the foreign key and referenced key. The size and sign of integer types must be the same. For character strings the character set and collation must be the same.
Consider the following example to understand this case:

CREATE TABLE student
(           
id TINYINT NOT NULL,                /* note the data type*/                  
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
Id INT,
student_id INT,         /* data type different from the referencing data field*/                  
INDEX stu_ind (student_id),                  
FOREIGN KEY (student_id)
REFERENCES student(id)                  
ON DELETE CASCADE
) ENGINE=INNODB;

Note that, in the above example, the data type of the id in student table is TINYINT but the data type of the student_id column in book table which referencing the student table.
Here you need to alter the data type of the student_id column in book table. You can alter the data type of the existing column using following statement:

mysql > ALTER TABLE book MODIFY COLUMN  Id TINY INT NOT NULL ;

 After altering the required fields, the new statement may look as follows:

CREATE TABLE student 
(                   
id TINYINT NOT NULL,                /* note the data type*/                  
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
id INT,
student_id TINYINT NOT NULL,                   /* data type same as the referencing data field*/      INDEX stu_ind (student_id),                  
FOREIGN KEY (student_id)
REFERENCES student(id)                  
ON DELETE CASCADE
ENGINE=INNODB;


Case 4: Foreign key as a primary key.

When you are implementing one-to-one relationship or composite primary key you may use foreign key as a primary key in your child table. Definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL. The following example will illustrate this case better:

CREATE TABLE user 
(                  
user_id INT NOT NULL,PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE student
(                  
user_id INT NOT NULL,             
PRIMARY KEY (user_id),           
FOREIGN KEY (user_id),                  
REFERENCES user (user_id),                  
ON DELETE CASCADE        /* Referential Action – ON DELETE not SET NULL */
) ENGINE=INNODB;










Case 5: Referential action – SET NULL.

Make sure when you specify SET NULL action, define the columns in the child table as NOT NULL.
The following example will explain this case clearly:

CREATE TABLE student
 (                   
id INT NOT NULL,
Reg_no varchar (255),                  
Key (Reg_no),          
PRIMARY KEY (id)
) ENGINE=INNODB; 

CREATE TABLE book
(                  
book_id INT,
reg_no varchar(255) NOT NULL,                 /* defined as NOT NULL*/                  
FOREIGN KEY (reg_no)
REFERENCES student(reg_no)                  
ON DELETE SET NULL                                        /*Action specified as SET NULL*/
) ENGINE=INNODB;

You can solve this by altering the foreign key column from not null to null. You can do that by using following statement:

mysql > ALTER TABLE book MODIFY reg_no varchar(255) ;
After modifying the table, the new statement may look similar to as follows:

CREATE TABLE student(
                  id INT NOT NULL,
                  Reg_no varchar (255),
                  Key (Reg_no),            
                  PRIMARY KEY (id)
)ENGINE=INNODB;
CREATE TABLE book
(
                  book_id INT, 
                  reg_no varchar(255) NULL,       /* allowed NULL*/
                  FOREIGN KEY (reg_no) 
                  REFERENCES student(reg_no)
                  ON DELETE SET NULL              /*Action specified as SET NULL*/
) ENGINE=INNODB;
I hope this post will help you to solve the mysql error code: 1215 cannot add foreign key constraint. If you still couldn’t figure out the issue, get in touch with me through contact me page, I will help you to solve this issue.

48 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Searched for the solution for few hours and then finally got it here, the case 5 worked for me. Nicely explained. Thanks for this post.

    ReplyDelete
  3. Sir my problem not solved, i am using mysql by phpmyadmin When i changed datatype length a table than it showing error and after that my table drop after that i import structure from backup it showing error 1215 while i dont have any foreign key in any table. Please reply as soon as possible.

    ReplyDelete
    Replies
    1. Hi Nitin,

      I hope you have fixed the issue by now. Please let me know, still you are facing the same issue.

      Delete
  4. Speedily this specific web page could without doubt become popular involving most writing a blog along with site-building persons, automobile thoughtful blogposts or possibly testimonials. windows 7 iso

    ReplyDelete
  5. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. agen bola online

    ReplyDelete
  6. Thank you very much for seeing 밤알바 information.
    Thank you very much for seeing 밤알바 information.

    ReplyDelete
  7. Thanks FOr SLoution.I tired to Find The Solution But Here My Search Is Complete.

    ReplyDelete
  8. This website content is more helpful. And thanks for share the information. Crack software!

    ReplyDelete
  9. Adobe Flash Builder Crack Premium Full Serial With Keygen Abobe Flash Builder 4.7 top quality Crack is just one of those massive players at the category of Flash IDEs, offering a professional progress environment suggested in making remarkable Software and re-creations for its internet adaptive or touch-empowered gadgets, as an instance, high-level cellphones and tablet computers.New Crack

    ReplyDelete
  10. Ccleaner-Pro serial key is a useful utility for computers with MS Windows. That cleans out all those junk that accumulates over time like broken shortcuts, temporary files, and many other problems. It is the best tool for cleaning your computer. As It makes your system work smoothly and fast. It also secures your system and protects your privacy.
    IDM Crack Setup

    ReplyDelete
  11. tiktok name generator
    TikTok Names

    best tiktok names

    aesthetic tiktok names

    tiktok creator name

    bio for tiktok

    tiktok usernames search

    alt tiktok username ideas

    musically name generator
    Loud Updates

    ReplyDelete

  12. 4k-video-downloader-crackis allows you to download high-quality videos, playlists, channels, and subtitles from YouTube, Facebook, Vimeo, and other video websites. Enjoy your videos anywhere, at any time, and, of course, even offline. The download is simple and direct.
    Free Pro Keys

    ReplyDelete
  13. 블로그 주문 시스템에 댓글을 달 수 있습니다. 멋진 채팅을해야합니다. 귀하의 블로그 감사는 방문자를 증가시킬 것입니다. 이 사이트를 발견하게되어 매우 기뻤습니다. 읽어 주셔서 감사합니다 !! 먹튀검증

    ReplyDelete
  14. Hello I want to share good information. Get good information. I will get good information. Everyone will have a hard time due to the corona, but please do your best. I hope that the corona will disappear soon. It would be hard for everyone, but I hope that the more I will endure and get good results. Thank you 메이저사이트

    ReplyDelete
  15. I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. 안전놀이터

    ReplyDelete
  16. This is a truly good site post. Not too many people would actually, the way you just did. I am really impressed that there is so much information about this subject that have been uncovered and you’ve done your best, with so much class. If wanted to know more about green smoke reviews, than by all means come in and check our stuff. 먹튀신고

    ReplyDelete
  17. ❤ I favor the idea, such a good deal buy traffic

    ReplyDelete
  18. ❤ I favor the idea, such a good deal 토토

    ReplyDelete
  19. The information you have posted is very useful. The sites you have referred was good. Thanks for sharing 먹튀폴리스

    ReplyDelete
  20. This is really a nice and informative, containing all information and also has a great impact on the new technology. Thanks for sharing it, windshield repair san diego

    ReplyDelete
  21. wow muy impresionante, nunca he visto algo así antes Seo Ottawa

    ReplyDelete
  22. The post is really superb. It’s varied accessory information that consists during a basic and necessary method. Thanks for sharing this text. The substance is genuinely composed. This web do my paper for me log is frequently sharing useful actualities. Keep sharing a lot of posts. yamaha dealer

    ReplyDelete
  23. I got too much interesting stuff on your blog. I guess I am not the only one having all the enjoyment here! Keep up the good work 사설토토

    ReplyDelete
  24. East london locksmith services is an established 24 hour Hackney locksmith company. Whether you have been locked out, lost your keys or require a lock replaced, call us and we will send out a certified engineer. who will aim to attend within 30 minutes of your call. Hackney Loksmith

    ReplyDelete
  25. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful. Auto Shop Near Me

    ReplyDelete
  26. "Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective to the topic which i was researching for a long time

    " Cheap Cars For Sale

    ReplyDelete
  27. "I am interested in such topics so I will address page where it is cool described.

    " Airport Taxi Service

    ReplyDelete
  28. If you want to play mega888 online slot and casino can visit this trusted mega888 website 2021. One of the most popular and trusted online slot games for many customers is mega888 malaysia this game is available for Android & IOS it is also the oldest games around it, and easy to download from other online slot games, is also one of the slot casino website online in the most popular and popular online poker table in Malaysia. https://www.lotusgame.org/what-you-need-to-know-about-mega888/

    ReplyDelete
  29. It is an excellent blog, I have ever seen. I found all the material on this blog utmost unique and well written. And, I have decided to visit it again and again. 검증업체

    ReplyDelete
  30. I think this is a really good article. You make this information interesting and engaging. You give readers a lot to think about and I appreciate that kind of writing. 먹튀검증

    ReplyDelete
  31. Well we really like to visit this site, many useful information we can get here. 토토사이트

    ReplyDelete
  32. I got a web site from where I be capable of really obtain valuable information regarding my study and knowledge.
    Great Article… Good Job… Thanks For Sharing…

    Website:대구출장안마


    ReplyDelete
  33. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place. 먹튀폴리스

    ReplyDelete
  34. 블로그 주문 시스템에 댓글을 달 수 있습니다. 멋진 채팅을해야합니다. 귀하의 블로그 감사는 방문자를 증가시킬 것입니다. 이 사이트를 발견하게되어 매우 기뻤습니다. 읽어 주셔서 감사합니다 !! 먹튀검증

    ReplyDelete
  35. 이 게시물을 읽는 것이 정말 즐거웠습니다. 좋은 작업을 계속하고 언제 더 많은 기사를 게시 할 수 있는지 또는 주제에 대한 자세한 내용을 어디에서 읽을 수 있는지 알려주십시오. 먹튀검증

    ReplyDelete
  36. Incredible! This blog lo?ks exactly ?ike my o?d one!
    It’s on a totally d?fferent subject b?t it ?as pretty muc?
    t?e ?ame layout ?nd design. Outstanding choice ?f colors!
    풀싸롱


    ReplyDelete
  37. Get Your Custom Socks Boxes – Wholesale Socks Packaging Boxes with Logo Made in Custom Shapes, sizes, and layouts. We offer quality and error-free packaging services with free shipping in all the UK. Today nobody wishes a hideous socks box on the shelves Our expert designers will come up tempting looks of socks boxes for you so your customers can complement your brand. We use a superior material for formulating custom printed paper socks boxes that retain them attractive for a long time.

    ReplyDelete
  38. I got a web site from where I be capable of really obtain valuable information regarding my study and knowledge.
    Great Article… Good Job… Thanks For Sharing…

    Website:출장안마


    ReplyDelete
  39. That's a wonderful piece of writing. Not as good as you, but I'm wearing it like this. Take a look.먹튀검증

    ReplyDelete
  40. Hello
    Thank you for giving me useful information.
    Please keep posting good information in the future
    I will visit you often. Thank you.
    I am also running the site. 메이저놀이터 This is a related site, so please visit once.
    Have a niceday!

    ReplyDelete
  41. This amazing hearings completely acceptable. Most of simple facts are ready through great number connected with practical knowledge realistic expertise. Now i am confident the item all over again completely. marketing advertising compannies

    ReplyDelete
  42. Took me time to read all the comments, 토토사이트 but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained!

    ReplyDelete