MySQL foreign keys on itself

Question

I am currently trying to create my own link in MySQL, but it seems that I cannot make the foreign key in the table itself, I get a MySQL error:

Error Code: 1005. Can't create table 'biological classification' (errno: 150) 

This is my code:

 # Table creation DROP TABLE IF EXISTS `biological classification`; CREATE TABLE `biological classification` ( `idBC` int(10) unsigned NOT NULL AUTO_INCREMENT, `idParent` int(11) DEFAULT NULL, `type` varchar(45) DEFAULT NULL, `value` varchar(45) DEFAULT NULL, PRIMARY KEY (`idBC`), UNIQUE KEY `idnew_table_UNIQUE` (`idBC`), CONSTRAINT `SelfKey` FOREIGN KEY (`idParent`) REFERENCES `biological classification` (`idBC`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='A table that contains the Biological Classification of anima'; # insert into table INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (1,NULL,'Class','Mammalia'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (2,1,'Genus','Giraffa'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (3,2,'Species','Giraffa camelopardalis'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (5,1,'Genus','Panthera'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (6,1,'Genus','Loxodonta'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (7,5,'Species','Panthera leo'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (8,5,'Species','Panthera tigris'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (9,6,'Species','Loxodonta africana'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (10,1,'Class','Marsupialia'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (11,10,'Genus','Macropus'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (12,11,'Species','Macropus rufus'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (13,1,'Genus','Sarcophilus'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (14,13,'Species','Sarcophilus harrisii'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (15,10,'Genus','Didelphis'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (16,15,'Species','Didelphis virginiana'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (17,NULL,'Class','Aves'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (18,17,'Genus','Aquila'); INSERT INTO `biological classification` (`idBC`,`idParent`,`type`,`value`) VALUES (19,18,'Species','Aquila chrysaetos'); 

Answer 1

Change the type from the idParent column to int(10) unsigned . So this is the same type as the idBC reference column.

 DROP TABLE IF EXISTS `biological classification`; CREATE TABLE `biological classification` ( `idBC` int(10) unsigned NOT NULL AUTO_INCREMENT, `idParent` int(10) unsigned DEFAULT NULL, `type` varchar(45) DEFAULT NULL, `value` varchar(45) DEFAULT NULL, PRIMARY KEY (`idBC`), UNIQUE KEY `idnew_table_UNIQUE` (`idBC`), CONSTRAINT `SelfKey` FOREIGN KEY (`idParent`) REFERENCES `biological classification` (`idBC`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='A table that contains the Biological Classification of anima'; 

Answer 2

Why don't you put a space in the table name?

Call it something like biological_classification with an underscore, not a space.

Then make two columns use the same data type: either int(10) or both int(11) and make them both signed and both unsigned (edited).