ERROR 1005 (HY000): Can't create table : How to Tackle

It's bit hard to identify the referential integrity issues in MySQL InnoDB engine  by looking at the error. It just throw the error in abstract, "Can't create table". But when we altering a table or adding a table with several foreign key constraints we are more prone to do such errors. So here I am going to explain a method which can be use to identify the error without going trough all the table syntax.

   

It's very simple. When you executing a bad foreign key sql query InnoDB engine will automatically saved it in its activities. So what you need to do is, asking the InnoDB engine to show its activities. Here is the sql query to dump out the InnoDB engine activities. 

mysql> SHOW engine InnoDB STATUS; 

When you execute it you will get a big output. You can go through it and find a topic "LATEST FOREIGN KEY ERROR" which has a clear description for your the error. Here is a sample output, I extracted from a error I was faced.

----------------------------------------------
LATEST FOREIGN KEY ERROR
----------------------------------------------
130529  3:55:45 Error in foreign key constraint of table mydatabase/mytable:

   FOREIGN KEY (`CountryName` )
   REFERENCES `mydatabase`.`country` (`countryName` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot resolve table name close to:
 (`Country` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

As in the above description say, the fault is, the InnoDB engine cannot find a table. Basically the country table which has a column countryName which reference by one of the columns in mytable table, does not exists in the database. Like wise you can get an idea about the error using the output of that command. Then you can go for a appropriate solution. 

Hope you use this for tackle your referential integrity issues.