MySql can't create table errno 121 – Solution!

MySql can't create table errno 121If you are working frequently with MySql databases, it is more likely that you have got this error many times. If you're reading this article to find how to solve this error, you are in the right place. Keep on reading to know how you will get this error and how you can solve this without getting any headaches :)

For those who haven't seen this before, here is the full error message:

Error 1005: Can't create table '' (errno: 121)

I wanted to find an exact solution for this error and write it on my blog so that it will be a reference for others as well.

I referred to the MySql documentation and found this doc, and this error comes under the InnoDB errors section. So now we know this has something to do with InnoDB. Let's read the documentation a little more :)

As the documentation states;

14.21.5 InnoDB Error Codes

The following is a nonexhaustive list of common InnoDB-specific errors that you may encounter, with information about why each occurs and how to resolve the problem.

1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.
….........

This is it! The error code is 1005, but it says only about the error 150, which is a different case. I went through the comments and found some nice tips.

Reason for getting Error 1005: Can't create table (errno: 121):


You will get this error if you are trying to add a constraint name that is already used in somewhere else.

As it turns out, you can't have two foreign key constraints with the same name. Simply that's the issue.

If you are getting this error while adding a foreign key to a table, that means you have used that constraint in another table. So just make sure to change the name of your foreign key constraint and save the table. It will work nicely if you have no other conflicts. :)

How to know where else you have used this constraint?
You can run the following query to see where else your constraint is used. I found this in a stackoverflow answer.

SELECT
    constraint_name,
    table_name
FROM
    information_schema.table_constraints
WHERE
    constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
    constraint_name;


I hope this article will help you to get this error solved! Please do not forget to share this with your friends on Google+, Twitter or Facebook if you think this will be useful to them too :)

0 comments :

Post a Comment