[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.

26 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. Thanks FOr SLoution.I tired to Find The Solution But Here My Search Is Complete.

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

    ReplyDelete
  7. 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
  8. 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

  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. "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
  17. 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
  18. 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
  19. Well we really like to visit this site, many useful information we can get here. 토토사이트

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

    ReplyDelete
  21. 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
  22. 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