BWSnippet

mySQL FOREIGN KEY error

mySQL FOREIGN KEY error

handling the good old mySQL FOREIGN KEY errno 150


CREATE TABLE `my_stuff_tag` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `my_stuff_id` int(11) unsigned DEFAULT NULL,
  `tag_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `my_stuff_id` (`my_stuff_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `my_stuff_id_fk1` FOREIGN KEY (`my_stuff_id`) REFERENCES `my_stuff` (`id`),
  CONSTRAINT `tag_id_fk2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


This can happen for several reasons. But I have noticed one VERY common issue. If the table the key references has or does not have "unsigned" your declaration in the table with the key needs the same.

So if table my_stuff has id with unsigned in the table with the key I need to declaire `my_stuff_id` int(11) unsigned DEFAULT NULL. But if it does not, `my_stuff_id` int(11) DEFAULT NULL, would be the correct way.

This would be obvious, but because of the poor error reporting for FOREIGN KEY's I always forget this common error.

Tags

mySQL FOREIGN KEY error