CODEWITHSUNDEEP
mysqlmysql-error-1064
Jeg Bagus
Jeg Bagus

Reputation: 5075

Mysql errno: 150 Cannot create Table

i use mysql work bench to export sql from erd that i create, but when i try to importing it to mysql, i just get error 150 Cannot create table.

when i try to remove constrain of service child, its just work. but i need to keep those referenced.

and every table that reference to service table, also have this error too. where am i wrong ? 

CREATE  TABLE IF NOT EXISTS `service` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `seq` INT(11) NOT NULL ,
  `image` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE  TABLE IF NOT EXISTS `service_package` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `sc_id` INT(11) NOT NULL ,
  `lang` INT(11) NOT NULL ,
  `package` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `service_package_id` (`sc_id` ASC) ,
  CONSTRAINT `service_package_id`
    FOREIGN KEY (`sc_id` )
    REFERENCES `service` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

Upvotes: 2

Views: 3928

Answers (2)

juacala
juacala

Reputation: 2235

For others viewing this thread, here are some other reasons why this happens:

1) The parent column has to be indexed before you create the foreign key.

2) The parent column needs to exist (seems simple, but it doesn't tell you that's the problem, it just says Errno 150.

There are a number of other reasons too. See the link below for an exhaustive list:

MySQL Foreign Key Errors and Errno: 150

Upvotes: 0

Dan Soap
Dan Soap

Reputation: 10248

You're using ENGINE = MyISAM on the service table. Try using ENGINE = InnoDB instead (as you do on the service_package table).

Foreign keys do not work with the MyIsam storage engine.

Upvotes: 5

Related Questions