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