Issue when trying to create table for many to many relationship mySQL

Question

I am trying to create a table for a many to many relationship in mySQL. This is the sql for it:

CREATE TABLE `directoryprogrammetags` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `directoryprogramme_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL,
  `description` TEXT,
  `created` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`directoryprogramme_id`) REFERENCES directoryprogramme(id),
  FOREIGN KEY (`tag_id`) REFERENCES tag(id) 
) ENGINE=INNODB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

But I am getting this error:

Error Code: 1005
Can't create table 'mytestdatabase.directoryprogrammetags' (errno: 150)

The tables that the foreign keys reference are:

CREATE TABLE `directoryprogramme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1200 DEFAULT CHARSET=utf8

and

CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8

I read somewhere that it could be to do with an inconsistency with the foreign key but I am not sure. Can anyone help me out on this one?

Perhaps this will help: http://stackoverflow.com/questions/16227199/mysql-errno-150 — Gordon Linoff, Nov 05 '14 at 16:05

score 1 · Accepted Answer · answered Nov 05 '14 at 16:04

The error is because the directoryprogramme table that you are referencing was created using a different engine (MyISAM) than the rest of the tables (InnoDB). If you change the engine so it uses the same one then the error goes away:

CREATE TABLE `directoryprogramme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1200 DEFAULT CHARSET=utf8;
         ^^^^^ This is the difference

You can check in this sqlfiddle that it works when you use this code

score 0 · Answer 2 · answered Nov 05 '14 at 16:09

0

You cannot use foreign keys from InnoDB to MyISAM. Change the database engine on directoryprogramme to InnoDB, and I bet it'll work.

answered Nov 05 '14 at 16:09

mainstreetmark

694
3
12

Issue when trying to create table for many to many relationship mySQL

2 Answers2