Question

MySQL 5.1.59错了,造成以下表格:

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建立表象板(errno:150)

这有什么错误?

该手册说::

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.

它还说,允许外国主要提及同一表格:

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

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.

也许重要的是,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 ;

Answer 1

页: 1 这些数字并不相称。解决办法是将<条码> 括号_id改为int unsign。

页: 1 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.

注 “表格中的栏目和参考表格中的栏目不匹配”。

Answer 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!!!!
  ....

Answer 3

<代码>id为UNSignED,但并不一致。

Answer 4

这些领域必须相同。背景无

友情链接