0

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?

Mazzy
  • 1,901
  • 2
  • 16
  • 36
  • its an foreing key error, i think it have something to do with the : `FOREIGN KEY (`catagory`) REFERENCES `db`.`catagories` (`catagory`)` (trying to make the relation from the table to the same table) – Youness Aug 14 '14 at 10:11
  • @Youness , I want to have sub-catagories, in mysql it's allowed to have references to the same table - Check http://stackoverflow.com/questions/14138952/foreign-key-references-same-tables-column-cant-insert-values – Mazzy Aug 14 '14 at 10:12

1 Answers1

1

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;
Naktibalda
  • 13,705
  • 5
  • 35
  • 51