Что не так с Ограничением внешнего ключа в этой таблице

MySQL 5.1.59 выдает ошибку с этой таблицей create:

CREATE  TABLE IF NOT EXISTS `genre` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `abv` CHAR(3) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NULL DEFAULT NULL ,
  `name` VARCHAR(80) NOT NULL DEFAULT '' ,
  `parent_id` INT NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_genre_genre1` (`parent_id` ASC) ,
  CONSTRAINT `fk_genre_genre1`
    FOREIGN KEY (`parent_id` )
    REFERENCES `genre` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Что было создано MySQLWorkbench 5.2.33.

Сообщение об ошибке:

ERROR 1005 (HY000) at line __: Can't create table 'mydb.genre' (errno: 150)

Что случилось с этой таблицей create?

В руководстве указано:

Если MySQL сообщает номер ошибки 1005 из инструкции CREATE TABLE, и сообщение об ошибке относится к ошибке 150, неудача создания таблицы потому что ограничение внешнего ключа не было правильно сформировано.

В нем также говорится, что ссылки на внешние ключи для одной и той же таблицы разрешены:

InnoDB поддерживает ссылки на внешние ключи в таблице. В этих случаях, "записи дочерних таблиц" действительно ссылаются на зависимые записи в пределах та же таблица.

Отношения, которые я хочу, - это неидентифицирующий родитель-ребенок, представляющий иерархию жанров и поджанров. Жанр не должен иметь родителя, поэтому parent_id может быть нулевым.

Может быть, что MySQLWorkbench установил следующее:

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL';

Ответ 1

Ваша колонка id равна int unsigned; ваша колонка parent_id равна int. Те не совпадают. Решение состоит в том, чтобы изменить parent_id на int unsigned.

Если вы запустите SHOW ENGINE InnoDB STATUS, я добавлю комментарий, вы увидите следующее:

11005 17:18:38 Error in foreign key constraint of table test/genre:

    FOREIGN KEY (`parent_id` )
    REFERENCES `genre` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB:
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.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Обратите внимание на то, что "типы столбцов в таблице и ссылочная таблица не соответствуют".

Ответ 2

Два поля не одного типа.

CREATE  TABLE IF NOT EXISTS `genre` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,  <<-- unsigned int
  ..
  ..
  `parent_id` INT NULL DEFAULT NULL ,        <<-- signed int
  PRIMARY KEY (`id`) ,                   ***** not the same!!!!
  ....

Ответ 3

id UNSIGNED, но parent_id не является беззнаковым.

Ответ 4

Поля должны быть одного типа. id isignedigned, а parent_id не является