#1072 关键列不存在错误与 MySQL 外键 [英] #1072 key column doesn't exist error with MySQL foreign key

问题描述

我已经搜索了可能有用的答案，但没有解决.

I've searched for answers might work, but didn't fix.

我已经创建了这样的表，当创建表 'hobbies' 时出现错误 1072.

I've created tables like this and when created table 'hobbies' got error 1072.

CREATE  TABLE IF NOT EXISTS `project_r`.`user` (
  `user_id` INT NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `RealName` VARCHAR(45) NOT NULL ,
  `FamilyName` VARCHAR(45) NOT NULL ,
  `birthdate` VARCHAR(45) NOT NULL ,
  `Homecity` VARCHAR(45) NOT NULL ,
  `school_id` INT NOT NULL ,
  `school_enteryear` INT NOT NULL ,
  `email` VARCHAR(45) NULL ,
  `activestat` VARCHAR(45) NULL ,
  `onlinestat` VARCHAR(45) NULL ,
  `regtime` DATETIME NOT NULL ,
  `avatar` VARCHAR(45) NULL ,
  `status` VARCHAR(45) NULL ,
  `desc` VARCHAR(45) NULL ,
  `self_comment` VARCHAR(45) NULL ,
  `userMsg_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_user_school1_idx` (`school_id` ASC) ,
  CONSTRAINT `fk_user_school1`
    FOREIGN KEY (`school_id` )
    REFERENCES `project_r`.`school` (`school_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

然后创建表'爱好'

CREATE  TABLE IF NOT EXISTS `project_r`.`hobbies` (
  `hobby_user_id` INT NOT NULL ,
  `favorite_music` VARCHAR(45) NULL ,
  `favorite_game` VARCHAR(20) NULL ,
  `favorite_film` VARCHAR(45) NULL ,
  `favorite_book` VARCHAR(45) NULL ,
  `favorite_sport` VARCHAR(45) NULL ,
  `favorite_cartoon` VARCHAR(45) NULL ,
  `hobby` VARCHAR(45) NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_user_id` (`hobby_user_id` ASC) ,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`hobby_user_id` )
    REFERENCES `project_r`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

添加或不添加的 AUTO-INCREMENT 内容或添加到引用表的索引都不起作用.谢谢你的帮助.

both AUTO-INCREMENT things added or not or index added to referenced tables didn't work. thx for help.

推荐答案

您正在 hobbies 中的 user_id 列上设置 PRIMARY KEY，但没有这样的列.它被称为hobby_user_id.

You are setting a PRIMARY KEY on a column user_id in hobbies, but there is no such column. It is called hobby_user_id.

 PRIMARY KEY (`hobby_user_id`) ,

在我自己的数据库中进行的快速测试成功创建了这两个具有正确PRIMARY KEY 规范的表.

A quick test in my own database successfully creates these two tables with the correct PRIMARY KEY spec.

这篇关于#1072 关键列不存在错误与 MySQL 外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！