ERROR: Error 1005: Can't create table 'airbnb.profile' (errno: 150)

Question

I created a few tables with foreign keys in a workbench model that I wanted to forward engineer to a server connection although I am recieving this error;

ERROR: Error 1005: Can't create table 'airbnb.profile' (errno: 150)

I have had a look around on how to resolve this error but nothing has seemed to have worked. I tried ensuring all the default character sets were the same, that did not work.

I tried adding

SET FOREIGN_KEY_CHECKS=0;

This also did not work. I am new to mysql and database design in general so I am in a bit of a loss as to what exactly is wrong with my script.

Below is the SQL script, any help to resolve this error would be greatly appreciated.

-- MySQL Workbench Forward Engineering

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';

-- -----------------------------------------------------
-- Schema airbnb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema airbnb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `airbnb` DEFAULT CHARACTER SET latin1 ;
USE `airbnb` ;

-- -----------------------------------------------------
-- Table `airbnb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`users` (
  `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) CHARACTER SET 'utf8' NOT NULL,
  `last_name` VARCHAR(50) CHARACTER SET 'utf8' NOT NULL,
  `dob` DATETIME NOT NULL,
  `email` VARCHAR(320) NOT NULL,
  `newsletter` BIT(1) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  `credit` DECIMAL(10,2) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `airbnb`.`language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`language` (
  `language_id` INT NOT NULL AUTO_INCREMENT,
  `name` NVARCHAR(50) NOT NULL,
  PRIMARY KEY (`language_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`profile`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`profile` (
  `profile_id` INT(11) NOT NULL,
  `user_id` INT(10) NOT NULL,
  `gender` ENUM('male', 'female', 'other') NULL DEFAULT NULL,
  `phone_number` VARCHAR(45) NULL DEFAULT NULL,
  `bio` VARCHAR(1000) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `location` VARCHAR(300) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `site_currency` VARCHAR(3) CHARACTER SET 'utf8' NOT NULL,
  `language_id` INT NOT NULL,
  PRIMARY KEY (`profile_id`),
  UNIQUE INDEX `profile_id_UNIQUE` (`profile_id` ASC),
  INDEX `fk_profile_users1_idx` (`user_id` ASC),
  INDEX `fk_profile_language1_idx` (`language_id` ASC),
  CONSTRAINT `fk_profile_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `airbnb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_profile_language1`
    FOREIGN KEY (`language_id`)
    REFERENCES `airbnb`.`language` (`language_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `airbnb`.`profile_media`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`profile_media` (
  `profile_media_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) NOT NULL,
  `photo` VARCHAR(100) NULL DEFAULT NULL,
  `symbol` VARCHAR(100) NULL DEFAULT NULL,
  `video` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`profile_media_id`),
  INDEX `fk_profile_media_users1_idx` (`user_id` ASC),
  CONSTRAINT `fk_profile_media_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `airbnb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `airbnb`.`em_contact`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`em_contact` (
  `em_contact_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NULL,
  `phone` VARCHAR(45) NULL,
  `email` NVARCHAR(320) NULL,
  `relationship` VARCHAR(100) NULL,
  PRIMARY KEY (`em_contact_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`timezone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`timezone` (
  `timezone_id` INT NOT NULL AUTO_INCREMENT,
  `name` NVARCHAR(50) NOT NULL,
  PRIMARY KEY (`timezone_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`shipping_address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`shipping_address` (
  `ship_id` INT NOT NULL AUTO_INCREMENT,
  `add_1` NVARCHAR(50) NOT NULL,
  `add_2` NVARCHAR(50) NULL,
  `city` NVARCHAR(50) NOT NULL,
  `state` NVARCHAR(50) NOT NULL,
  `zip_code` NVARCHAR(20) NOT NULL,
  PRIMARY KEY (`ship_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`profile_opt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`profile_opt` (
  `prof_opt_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) NOT NULL,
  `school` VARCHAR(150) CHARACTER SET 'utf8' NULL,
  `work` VARCHAR(100) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `work_email_add` VARCHAR(320) NULL DEFAULT NULL,
  `country_of_residence_id` INT(11) NULL,
  `em_contact_id` INT NULL,
  `timezone_id` INT NOT NULL,
  `ship_id` INT NULL,
  PRIMARY KEY (`prof_opt_id`),
  UNIQUE INDEX `profile_optional_id_UNIQUE` (`prof_opt_id` ASC),
  INDEX `fk_profile_optional_users1_idx` (`user_id` ASC),
  INDEX `fk_profile_opt_em_contact1_idx` (`em_contact_id` ASC),
  INDEX `fk_profile_opt_timezone1_idx` (`timezone_id` ASC),
  INDEX `fk_profile_opt_shipping_address1_idx` (`ship_id` ASC),
  CONSTRAINT `fk_profile_optional_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `airbnb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_profile_opt_em_contact1`
    FOREIGN KEY (`em_contact_id`)
    REFERENCES `airbnb`.`em_contact` (`em_contact_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_profile_opt_timezone1`
    FOREIGN KEY (`timezone_id`)
    REFERENCES `airbnb`.`timezone` (`timezone_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_profile_opt_shipping_address1`
    FOREIGN KEY (`ship_id`)
    REFERENCES `airbnb`.`shipping_address` (`ship_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `airbnb`.`secure_login`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`secure_login` (
  `secure_id` INT(11) NOT NULL AUTO_INCREMENT,
  `encrypted_password` BINARY(32) NOT NULL,
  `password_salt` BINARY(64) NOT NULL,
  `login_count` INT(11) NOT NULL,
  `last_login_request` DATETIME NULL DEFAULT NULL,
  `last_login_at` DATETIME NULL DEFAULT NULL,
  `last_login_ip` VARBINARY(16) NULL DEFAULT NULL,
  `browser_id` INT(11) NULL DEFAULT NULL,
  `device_id` INT(11) NULL DEFAULT NULL,
  `user_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`secure_id`),
  UNIQUE INDEX `id_UNIQUE` (`secure_id` ASC),
  INDEX `fk_secure_login_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_secure_login_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `airbnb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `airbnb`.`hear_about_options`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`hear_about_options` (
  `selection_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `selection_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`selection_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`hear_about`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`hear_about` (
  `hear_about_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `selection_id` INT NOT NULL,
  PRIMARY KEY (`hear_about_id`),
  INDEX `fk_hear_about_users1_idx` (`user_id` ASC),
  INDEX `fk_hear_about_hear_about_options1_idx` (`selection_id` ASC),
  CONSTRAINT `fk_hear_about_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `airbnb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_hear_about_hear_about_options1`
    FOREIGN KEY (`selection_id`)
    REFERENCES `airbnb`.`hear_about_options` (`selection_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `airbnb`.`profile_language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `airbnb`.`profile_language` (
  `p_vs_l_id` INT NOT NULL AUTO_INCREMENT,
  `prof_opt_id` INT(11) NOT NULL,
  `language_id` INT NOT NULL,
  PRIMARY KEY (`p_vs_l_id`),
  INDEX `fk_profile_vs_language_profile_opt1_idx` (`prof_opt_id` ASC),
  INDEX `fk_profile_language_language1_idx` (`language_id` ASC),
  CONSTRAINT `fk_profile_vs_language_profile_opt1`
    FOREIGN KEY (`prof_opt_id`)
    REFERENCES `airbnb`.`profile_opt` (`prof_opt_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_profile_language_language1`
    FOREIGN KEY (`language_id`)
    REFERENCES `airbnb`.`language` (`language_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Possible duplicate of [MySQL Foreign Key Error 1005 errno 150](http://stackoverflow.com/questions/4063141/mysql-foreign-key-error-1005-errno-150) — Jorge Campos, Dec 12 '15 at 17:33
`user_id` has to be the exact same type in both tables,in `users` is unsigned while in `profile` is not — Mihai, Dec 12 '15 at 17:33
@Mihai I just realised it had user_id unsigned by accident. I removed it but I am still receiving the error??? — mcclosa, Dec 12 '15 at 17:39
Remove `UNIQUE INDEX profile_id_UNIQUE (profile_id ASC)` since you already have a primary key and make sure there are no other constraints or indexes with identical names — Mihai, Dec 12 '15 at 17:45

ERROR: Error 1005: Can't create table 'airbnb.profile' (errno: 150)

0 Answers0