CODEWITHSUNDEEP
mysqlsql
Javacadabra
Javacadabra

Reputation: 5758

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

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?

Upvotes: 0

Views: 66

Answers (2)

mainstreetmark
mainstreetmark

Reputation: 703

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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

Upvotes: 1

Related Questions