这个表中的外键约束有什么问题

MySQL 5.1.59 throws an error error with this create table:

MySQL 5.1.59使用此create table抛出错误：

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;

Which was generated by MySQLWorkbench 5.2.33.

这是由MySQLWorkbench 5.2.33生成的。

The error message is:

错误消息是：

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

What's wrong with this create table?

这个创建表出了什么问题？

The manual says:

手册说：

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

如果MySQL从CREATE TABLE语句报告错误号1005，并且错误消息引用错误150，则表创建失败，因为未正确形成外键约束。

It also says that foreign key references to the same table are allowed:

它还说允许对同一个表的外键引用：

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

InnoDB支持表中的外键引用。在这些情况下，“子表记录”实际上是指同一个表中的依赖记录。

The relationship I want is a non-identifying parent-child to represent a hierarchy of genres and sub-genres. A genre doesn't have to have a parent, hence parent_id is nullable.

我想要的关系是一个非识别的父子，代表一个流派和子流派的层次结构。类型不必具有父类，因此parent_id可以为空。

It may be relevant that MySQLWorkbench set the following:

MySQLWorkbench设置以下内容可能是相关的：

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

4 个解决方案

#1

Your column id is int unsigned; your column parent_id is int. Those don't match. The solution is to change parent_id to be int unsigned as well.

你的列id是int unsigned;你的列parent_id是int。那些不匹配。解决方案是将parent_id更改为int unsigned。

If you run the SHOW ENGINE InnoDB STATUS I put in a comment, you see this:

如果您运行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.

Note the "column types in the table and the referenced table do not match" part.

请注意“表中的列类型和引用的表不匹配”部分。

#2

The two fields are not the same type.

这两个字段的类型不同。

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 is UNSIGNED but parent_id is not unsigned.

id是UNSIGNED，但parent_id不是无符号的。

#4

The fields must be the same type. id is unsigned whereas parent_id is not

字段必须是相同的类型。 id是无符号的，而parent_id则不是

#1