Can't create table (errno: 150) with added KEY

Question

I have a problem with adding part of db to ready db. It looks like:

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

-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites_users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idusers` INT NOT NULL,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,
  PRIMARY KEY (`id`, `idsite`, `idusers`),
  INDEX `fk_diplom_sites_users_diplom_users1_idx` (`idusers` ASC),
  CONSTRAINT `fk_diplom_sites_users_diplom_users1`
    FOREIGN KEY (`idusers`)
    REFERENCES `kreker92_diplom`.`diplom_users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idsite` INT NOT NULL,
  `siteurl` VARCHAR(200) NOT NULL,
  `mobsiteurl` VARCHAR(200) NOT NULL,
  `uridir` VARCHAR(100) NOT NULL,
  `sitetype` VARCHAR(45) NOT NULL,
  `panelstyle` VARCHAR(45) NOT NULL,
  `publish` TINYINT(1) NOT NULL,
  `multilang` TINYINT(1) NOT NULL,
  `callbtn` TINYINT(1) NOT NULL,
  `sinchronization` TIME NULL,
  PRIMARY KEY (`id`, `idsite`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,
  `sites_data_idsite` INT NOT NULL,
  KEY ix_diplom_sites_idsite (idsite),
  PRIMARY KEY (`id`, `idsite`),
  CONSTRAINT `fk_sites_users`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_users` (`idsite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sites_sites_data1`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_data` (`idsite`)
    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;

But phpMyAdmin sad that:

1005 - Can't create table 'kreker92_diplom.diplom_sites' (errno: 150)

abd it starts from:

-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites`
-- -----------------------------------------------------

But i've made a KEY:

KEY ix_diplom_sites_idsite (idsite),

So what is wrong I make?

Ravinder Reddy · Answer 1 · 2014-06-07T02:48:37.033

For a field to be a foreign key its parent field must have an 'INDEX' defined on it.
As per documentation on foreign key constraints:

REFERENCES tbl_name (index_col_name,...)

In 'diplom_sites' table,

CONSTRAINT `fk_sites_users`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_users` (`idsite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

the referred parent field 'diplom_sites_users( idsite )' has not 'key' defined on it was only part of a composite key. Define a separate key on it.

CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idusers` INT NOT NULL,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,

  INDEX `ix_users_idsite`( `idsite` ), -- <------ this was missing

  PRIMARY KEY (`id`, `idsite`, `idusers`),
  INDEX `fk_diplom_sites_users_diplom_users1_idx` (`idusers` ASC),
  CONSTRAINT `fk_diplom_sites_users_diplom_users1`
    FOREIGN KEY (`idusers`)
    REFERENCES `kreker92_diplom`.`diplom_users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Refer to:

MySQL Using FOREIGN KEY Constraints

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

Thanks for ur helping! I've added INDEX `ix_diplom_sites_users_idsite`( `idsite` ), where u say but I still have the same error — user3713526, Jun 07 '14 at 03:47
Add similar indexes for other referred columns too. For example, `diplom_sites_data.idsite` too needs an index defined. You have to define indexes for all such referred fields. — Ravinder Reddy, Jun 07 '14 at 04:00

Can't create table (errno: 150) with added KEY

1005 - Can't create table 'kreker92_diplom.diplom_sites' (errno: 150)

1 Answers1