Databases operations like backup or restore often result in annoying errors.

Usually, MySQL error 1215 pops up while creating a foreign key or constraints in the database table.

There are many reasons for this error to occur which include in-appropriate quotes in the constraints, missing table, parent table doesn’t use InnoDB and many more.

At Bobcares, we receive requests on MySQL errors as a part of our Server Management Services.

Today, let’s see what causes MySQL error 1215 and how our Support Engineers fix it.

What causes MySQL error 1215?

Let’s have a first look at the MySQL error 1215. Recently one of our customers reported a MySQL error. He was trying to create a relational database table with 3 primary keys. But, this resulted in the error:

There can be many causes for this error to occur. Let’s now check out few causes that our Database Experts usually see in databases.

1. The table does not exist

Sometimes, the database table to which customers try to add constraints doesn’t exist. The results in 1215 error. However, there will be no hint given that the table doesn’t exist. So its advisable to check the available tables in the database using the command:

show tables;

Through this, we can confirm if the tables are available or not.

2. Parent table is not using InnoDB

If the storage engine of the parent table is not InnoDB then also it will throw errors. So it is necessary to check the engine of the table. And then we can set it to InnoDB if it not set.

We use the below command to change the storage engine of the tables in case if there is any change required.

ALTER TABLE parent ENGINE=INNODB;

Ideally, our Support Engineers suggest customers make sure of using InnoDB as the engine on all tables in the database.

3. In-appropriate quotes to the tables in constraints

Normally, while running commands to alter the tables wrong quotes will be provided. This will run into problems and finally end up with a MySQL error 1215.

Thus, it is really important to avoid any syntax errors while executing MySQL queries.

4. A typo in the local key and foreign table

All the details in both the local key and foreign tables must have the same variables. If there are any typo errors while running the command then the final result will end into an error. Again, if the Primary key or Foreign Key is varchar, we always make sure that the collation is the same for both.

How we fix MySQL error 1215?

We have now discussed the different causes of this error message.

Let’s see how our Support Engineers fix this error.

1. Check for the table availability

Our Support Engineers started troubleshooting the error by checking if the table is available. We used the below command for it.

show tables;

We found that the table was available to create the constraint,

2. Check if the parent table is using InnoDB

We further continued to investigate by checking the storage engine set to the database table. We used the below command for it.

SHOW CREATE TABLE parent

As a result, we found that the storage engine was not InnoDB. It was set to ENGINE=MYISAM. So we changed the storage engine using the command:

ALTER TABLE parent ENGINE=INNODB;

Finally, this fixed the error.

3. Check if there are any in-appropriate quotes

We also confirm if our customers are running the right command. We check all the details like the quotes in the command used as well.

Here is an example, where our customer used the quotes incorrectly in the command and ran into problems.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES `parent(id)`;

We corrected the command and provided the right one below.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES `parent`(`id`);

Also, it can be without quotes as below.

ALTER TABLE child ADD FOREIGN KEY (parentid) REFERENCES parent(id);

Lastly, running the right command fixed the error.

4. Verifying the existence of key

While entering the MySQL commands it is necessary to enter the right command.  We cross-check and see if the customer is trying to reference a nonexistent key on the target table.

Therefore, we correct the customers to use the key that really exists on the table. In some cases, the column in use for ON DELETE SET NULL may not be defined to be null. So we make sure that the column is set default null. This prevents the MySQL error 1215.

[Need any assistance with MySQL errors? – We’ll help you]

Conclusion

In short, the MySQL error 1215 is caused due to many reasons which mainly include in-appropriate quotes in the command, non-existence of the table in the constraint, parent table not using InnoDB and many more. Today, we saw the different causes and how our Support Engineers fix it.