Month: April 2008

Full error message: #1005 – Can’t create table ‘.\my_db\#sql-7c4_444.frm’.

Using MySQL version 4.1.22-community-nt, I created two tables (`table1` and `table2`) in a database (let’s call it `my_db`) as follows:

CREATE TABLE `table1` (
`idtable1` int(10) unsigned NOT NULL auto_increment,
`table1_str` varchar(50) NOT NULL default '',
PRIMARY KEY  (`idtable1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
`idtable2` int(10) unsigned NOT NULL auto_increment,
`idtable1_fk` int(10) NOT NULL,
`table2_str` varchar(50) NOT NULL default '',
PRIMARY KEY  (`idtable2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I tried to add a foreign key constraint like this:

ALTER TABLE `table2`
ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`idtable1_fk`) REFERENCES `table1` (`idtable1`) ON DELETE CASCADE ON UPDATE CASCADE;

And then I got the error.

The solution in my case was adding the unsigned attribute to the `idtable1_fk` field in `table2` for having the same type as the field `idtable1` it references in `table1`:

ALTER TABLE `table2` CHANGE `idtable1_fk` `idtable1_fk` int(10) unsigned NOT NULL;

After that I ran the foreign key constraint query:

ALTER TABLE `table2`
ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`idtable1_fk`) REFERENCES `table1` (`idtable1`) ON DELETE CASCADE ON UPDATE CASCADE;

And then it worked. But if you don’t have the same problem but you get the same error message you might already have a foreign key constraint with the given name. For example, if I run again the foreign key constraint query I will get the same error. Be careful if you use phpMyAdmin as a visual interface for MySQL, because it seems to me that it does not show us all the foreign key constraints we added to a table. If you want to see which foreign key constraints a table has, you could export the structure of that table and analyze the foreign key constraints queri(es).

Hope this works for you, too.

Conclusion: We should make sure that the possible values of the foreign key field are in the same range as the possible values of the field referenced by that foreign key and that there is no foreign key constraint with the same name as the constraint we are trying to add.