doulu8847
2012-03-04 03:10 阅读 27
已采纳

无法在Mysql上创建表errno:150

i get another errno 150 on mysql. i already look at table engine, column type, but no luck, its nothing wrong in my view.

where im going wrong this time?

i get error when creating tag table that relate to image_tag and tag_lang.

-- -----------------------------------------------------
-- Table `ratna`.`image_tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ratna`.`image_tag` ;

CREATE  TABLE IF NOT EXISTS `ratna`.`image_tag` (
  `id` INT(11) NOT NULL ,
  `tag` INT(11) NOT NULL ,
  PRIMARY KEY (`id`, `tag`) ,
  INDEX `image_fk` (`id` ASC) ,
  CONSTRAINT `image_fk`
    FOREIGN KEY (`id` )
    REFERENCES `ratna`.`image` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ratna`.`tag_lang`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ratna`.`tag_lang` ;

CREATE  TABLE IF NOT EXISTS `ratna`.`tag_lang` (
  `id` INT(11) NOT NULL ,
  `lang` INT(20) NOT NULL ,
  `tag_desc` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  PRIMARY KEY (`id`, `lang`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ratna`.`tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ratna`.`tag` ;

CREATE  TABLE IF NOT EXISTS `ratna`.`tag` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `seq` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `image_tag_fk` (`id` ASC) ,
  INDEX `tag_lang` (`id` ASC) ,
  CONSTRAINT `image_tag_fk`
    FOREIGN KEY (`id` )
    REFERENCES `ratna`.`image_tag` (`tag` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `tag_lang`
    FOREIGN KEY (`id` )
    REFERENCES `ratna`.`tag_lang` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 13
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    douba2705 douba2705 2012-03-04 03:20

    This:

        REFERENCES `ratna`.`image_tag` (`tag` )
    

    is not valid, because image_tag does not have any index that begins with tag. As explained in the documentation:

    InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

    ("The referenced table" in this case is image_tag, and "the referenced columns" are just tag.)

    点赞 评论 复制链接分享
  • dounabi6295 dounabi6295 2012-03-04 03:16

    First google result for mysql error 150 shows:

    If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

    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. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

    http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

    点赞 评论 复制链接分享
  • douju6651 douju6651 2013-03-25 07:42

    in my case it was issue of

    foreign key (table_column_name_in_smaller_case) references primary_key_table_in_upper_case (table_column_name_in_smaller_case)

    since my primary key table is in Lower case, i changed this upper foreign key reference from

    *primary_key_table_in_upper_case*

    to

    *primary_key_table_in_lower_case*

    and it worked

    点赞 评论 复制链接分享

相关推荐