CODEWITHSUNDEEP
mysqlinnodb
Mazzy
Mazzy

Reputation: 1944

SQL Can't create table errno 150

I made an ERD with MySQL Workbench and now I'm trying to import it,

The first table it tries to create it errors.

The sql:

    CREATE TABLE IF NOT EXISTS `db`.`catagories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `catagory` INT UNSIGNED NULL,
  `order` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_catagory_idx` (`catagory` ASC),
  CONSTRAINT `fk_catagory`
    FOREIGN KEY (`catagory`)
    REFERENCES `db`.`catagories` (`catagory`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;

1005 - Can't create table 'db.catagories' (errno: 150)

both catagory and ID have an index, are the same, but it still throws this error, any thoughts?

Upvotes: 0

Views: 54

Answers (1)

Naktibalda
Naktibalda

Reputation: 14102

Your foreign key constraint is invalid, column catagory references to itself.

Edit: answer to comment below.

If you want to reference a parent category, reference id field.

 CREATE TABLE IF NOT EXISTS `db`.`catagories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `parent_catagory` INT UNSIGNED NULL,
  `order` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_catagory_idx` (`parent_catagory` ASC),
  CONSTRAINT `fk_catagory`
    FOREIGN KEY (`parent_catagory`)
    REFERENCES `db`.`catagories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;

Upvotes: 1

Related Questions