MySQL foreign keys on self

Question

I'm currently trying to make a self referencing table in MySQL, however it seems I can't make a foreign key on the table itself, I'm getting an 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');

score 20 · Accepted Answer · answered Aug 29 '11 at 12:42

Change the type from column idParent to int(10) unsigned. So it is the same type like the referenced column idBC.

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';

I know this is rather old but for googlers: I had a similar situation. I have a couple of tables with id fields int(11) in all of them. No "unsigned" in any of them, so they are all similar. However, mysql rejected creating self referencing tables. Others have been created. After reading this page, I added unsigned to all of them and then all worked OK. I think although all id fields were exactly same, if you make some of them "autoincrement" they are probably accepted as "unsigned". Possibly that was the problem for me. Thank you. — Malkocoglu, Jul 11 '15 at 08:52

score 13 · Answer 2 · edited Nov 13 '15 at 19:54

13

Why on earth would you put a space in a table name???

Call it something like biological_classification with an underscore rather than a space.

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

edited Nov 13 '15 at 19:54

ChrisF

134,786
31
255
325

answered Aug 29 '11 at 12:43

Bohemian

412,405
93
575
722

It's not the int(10) or int(11) that needs to line up, it's the signed and unsigned. – Johan Aug 29 '11 at 13:06
15

Because I havn't learned that its bad pratice to put spaces in table names, could you explain to me why? - And please remain calm, I'm new to MySQL. – Skeen Aug 29 '11 at 13:11
7

@Skeen It's about general good practise for readability and comfort. With spaces in names you often have to deal with brackets and other things making it often more complex than it really is. Avoiding spaces in identifiers is a general good practise not just for SQL. – Fabian Barney Aug 29 '11 at 13:24

MySQL foreign keys on self

2 Answers2

Linked