I have created a EER model using MySQL Workbench (latest release). Apparently all is fine but when I try to do a FORWARD ENGINEER I get the common Error 1005: Can't create table. As suggested in this post I check every step on the first answer but without success, can any give some extra eyes to my query and try to find where my error is? I can't see it.
This is the error generated by the tool:
Executing SQL script in server
ERROR: Error 1005: Can't create table
'portal_de_logistica.order_has_comment' (errno: 150)
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`order_has_comment` (
`user_id` INT(11) NOT NULL,
`no_order` VARCHAR(15) NOT NULL,
`comment` TEXT NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `no_order`),
CONSTRAINT `fk_fos_user_rnd`
FOREIGN KEY (`user_id`)
REFERENCES `portal_de_logistica`.`fos_user_user` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_tb_order_rnd`
FOREIGN KEY (`no_order`)
REFERENCES `portal_de_logistica`.`tb_order` (`no_order`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci
And this is the complete SQL sentences:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
DROP SCHEMA IF EXISTS `portal_de_logistica` ;
CREATE SCHEMA IF NOT EXISTS `portal_de_logistica` DEFAULT CHARACTER SET latin1 ;
USE `portal_de_logistica` ;
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`company`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`company` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`company` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`register_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`country` CHAR(3) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`contact`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`contact` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`contact` (
`id` INT NOT NULL AUTO_INCREMENT,
`company_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`lastname` VARCHAR(90) NOT NULL,
`c_position` VARCHAR(150) NULL,
`email` VARCHAR(150) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_contact_company1`
FOREIGN KEY (`company_id`)
REFERENCES `portal_de_logistica`.`company` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB;
CREATE INDEX `fk_contact_company1_idx` ON `portal_de_logistica`.`contact` (`company_id` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`tb_order`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`tb_order` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`tb_order` (
`no_order` VARCHAR(15) NOT NULL,
`company_id` INT NOT NULL,
`business_case` VARCHAR(15) NULL,
`charge_status` ENUM('0','1','2','3','4','5','6','7','8') NOT NULL COMMENT '0: Ninguno\n1: Proceso de Fabricacion\n2: Pickup en destino\n3: A la espera de recojo por cliente\n4: Carga en transito\n5: Carga arribada\n6: En proceso de aduana\n7: Entregado a cliente\n8: En bodega',
`eta` DATE NULL,
`etd` DATE NULL,
`transport_media` ENUM('0','1','2') NOT NULL COMMENT '0: EXW\n1: Maritimo' /* comment truncated */ /*2: Aereo*/,
`incoterm` ENUM('0','1','2','3','4') NULL COMMENT '0: Ninguno\n1: EWX\n2: FOB\n3: CIF' /* comment truncated */ /*4: DDP*/,
PRIMARY KEY (`no_order`),
CONSTRAINT `fk_order_company1`
FOREIGN KEY (`company_id`)
REFERENCES `portal_de_logistica`.`company` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB;
CREATE UNIQUE INDEX `no_order_UNIQUE` ON `portal_de_logistica`.`tb_order` (`no_order` ASC);
CREATE INDEX `fk_order_company1_idx` ON `portal_de_logistica`.`tb_order` (`company_id` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`fos_user_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`fos_user_group` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`fos_user_group` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`roles` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '(DC2Type:array)',
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX `UNIQ_583D1F3E5E237E06` ON `portal_de_logistica`.`fos_user_group` (`name` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`fos_user_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`fos_user_user` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`fos_user_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`username_canonical` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`email` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`email_canonical` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`enabled` TINYINT(1) NOT NULL,
`salt` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`password` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`last_login` DATETIME NULL DEFAULT NULL,
`locked` TINYINT(1) NOT NULL,
`expired` TINYINT(1) NOT NULL,
`expires_at` DATETIME NULL DEFAULT NULL,
`confirmation_token` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`password_requested_at` DATETIME NULL DEFAULT NULL,
`roles` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '(DC2Type:array)',
`credentials_expired` TINYINT(1) NOT NULL,
`credentials_expire_at` DATETIME NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME NOT NULL,
`date_of_birth` DATETIME NULL DEFAULT NULL,
`firstname` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`lastname` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`website` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`biography` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`gender` VARCHAR(1) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`locale` VARCHAR(8) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`timezone` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`phone` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`facebook_uid` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`facebook_name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`facebook_data` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:json)',
`twitter_uid` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`twitter_name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`twitter_data` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:json)',
`gplus_uid` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`gplus_name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`gplus_data` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:json)',
`token` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`two_step_code` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX `UNIQ_C560D76192FC23A8` ON `portal_de_logistica`.`fos_user_user` (`username_canonical` ASC);
CREATE UNIQUE INDEX `UNIQ_C560D761A0D96FBF` ON `portal_de_logistica`.`fos_user_user` (`email_canonical` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`fos_user_user_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`fos_user_user_group` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`fos_user_user_group` (
`user_id` INT(11) NOT NULL,
`group_id` INT(11) NOT NULL,
PRIMARY KEY (`user_id`, `group_id`),
CONSTRAINT `FK_B3C77447FE54D947`
FOREIGN KEY (`group_id`)
REFERENCES `portal_de_logistica`.`fos_user_group` (`id`)
ON DELETE CASCADE,
CONSTRAINT `FK_B3C77447A76ED395`
FOREIGN KEY (`user_id`)
REFERENCES `portal_de_logistica`.`fos_user_user` (`id`)
ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX `IDX_B3C77447A76ED395` ON `portal_de_logistica`.`fos_user_user_group` (`user_id` ASC);
CREATE INDEX `IDX_B3C77447FE54D947` ON `portal_de_logistica`.`fos_user_user_group` (`group_id` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`media__gallery`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`media__gallery` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`media__gallery` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`context` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`default_format` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`enabled` TINYINT(1) NOT NULL,
`updated_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`media__media`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`media__media` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`media__media` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`description` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`enabled` TINYINT(1) NOT NULL,
`provider_name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`provider_status` INT(11) NOT NULL,
`provider_reference` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
`provider_metadata` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:json)',
`width` INT(11) NULL DEFAULT NULL,
`height` INT(11) NULL DEFAULT NULL,
`length` DECIMAL(10,0) NULL DEFAULT NULL,
`content_type` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`content_size` INT(11) NULL DEFAULT NULL,
`copyright` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`author_name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`context` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
`cdn_is_flushable` TINYINT(1) NULL DEFAULT NULL,
`cdn_flush_at` DATETIME NULL DEFAULT NULL,
`cdn_status` INT(11) NULL DEFAULT NULL,
`updated_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`media__gallery_media`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`media__gallery_media` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`media__gallery_media` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`gallery_id` INT(11) NULL DEFAULT NULL,
`media_id` INT(11) NULL DEFAULT NULL,
`position` INT(11) NOT NULL,
`enabled` TINYINT(1) NOT NULL,
`updated_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `FK_80D4C541EA9FDD75`
FOREIGN KEY (`media_id`)
REFERENCES `portal_de_logistica`.`media__media` (`id`),
CONSTRAINT `FK_80D4C5414E7AF8F`
FOREIGN KEY (`gallery_id`)
REFERENCES `portal_de_logistica`.`media__gallery` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX `IDX_80D4C5414E7AF8F` ON `portal_de_logistica`.`media__gallery_media` (`gallery_id` ASC);
CREATE INDEX `IDX_80D4C541EA9FDD75` ON `portal_de_logistica`.`media__gallery_media` (`media_id` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`fos_user_user_has_company`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`fos_user_user_has_company` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`fos_user_user_has_company` (
`fos_user_user_id` INT(11) NOT NULL,
`company_id` INT NOT NULL,
PRIMARY KEY (`fos_user_user_id`, `company_id`),
CONSTRAINT `fk_fos_user_user_has_company_fos_user_user1`
FOREIGN KEY (`fos_user_user_id`)
REFERENCES `portal_de_logistica`.`fos_user_user` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_fos_user_user_has_company_company1`
FOREIGN KEY (`company_id`)
REFERENCES `portal_de_logistica`.`company` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX `fk_fos_user_user_has_company_company1_idx` ON `portal_de_logistica`.`fos_user_user_has_company` (`company_id` ASC);
CREATE INDEX `fk_fos_user_user_has_company_fos_user_user1_idx` ON `portal_de_logistica`.`fos_user_user_has_company` (`fos_user_user_id` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`order_has_media__media`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`order_has_media__media` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`order_has_media__media` (
`order_no_order` VARCHAR(15) NOT NULL,
`media__media_id` INT(11) NOT NULL,
PRIMARY KEY (`order_no_order`, `media__media_id`),
CONSTRAINT `fk_order_has_media__media_order1`
FOREIGN KEY (`order_no_order`)
REFERENCES `portal_de_logistica`.`tb_order` (`no_order`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_order_has_media__media_media__media1`
FOREIGN KEY (`media__media_id`)
REFERENCES `portal_de_logistica`.`media__media` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_order_has_media__media_media__media1_idx` ON `portal_de_logistica`.`order_has_media__media` (`media__media_id` ASC);
CREATE INDEX `fk_order_has_media__media_order1_idx` ON `portal_de_logistica`.`order_has_media__media` (`order_no_order` ASC);
-- -----------------------------------------------------
-- Table `portal_de_logistica`.`order_has_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `portal_de_logistica`.`order_has_comment` ;
CREATE TABLE IF NOT EXISTS `portal_de_logistica`.`order_has_comment` (
`user_id` INT(11) NOT NULL,
`no_order` VARCHAR(15) NOT NULL,
`comment` TEXT NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `no_order`),
CONSTRAINT `fk_fos_user_rnd`
FOREIGN KEY (`user_id`)
REFERENCES `portal_de_logistica`.`fos_user_user` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_tb_order_rnd`
FOREIGN KEY (`no_order`)
REFERENCES `portal_de_logistica`.`tb_order` (`no_order`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX `fk_fos_user_user_has_tb_order_tb_order1_idx` ON `portal_de_logistica`.`order_has_comment` (`no_order` ASC);
CREATE INDEX `fk_fos_user_user_has_tb_order_fos_user_user1_idx` ON `portal_de_logistica`.`order_has_comment` (`user_id` ASC);
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
EDIT: Add more info
Following @Up_One answer I run the command and get this as output:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140225 3:51:56 Error in foreign key constraint of table portal_de_logistica/order_has_comment:
FOREIGN KEY (`no_order`)
REFERENCES `portal_de_logistica`.`tb_order` (`no_order`)
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
Best Answer
This error is because of you innodb engine referenced constraint
read the article from this link - this will fix your problem
Or you can read my answer on this page ! Because of you referential integrity on ENGINE = InnoDB, you need to make sure that the referenced table must carry the same engine type.
To find out if this is your case use the command bellow :
This command will list the dump log of your innodb engine.
, if this is your case make sure you fix the engine type by altering the table to carry the same engine when referencing one another!
Example of out put :