没有可能的时间来查找此错误是什么。
我用
SHOW ENGINE INNODB STATUS
得到这个错误:
2015-12-05 03:20:44 16c8 Error in foreign key constraint of table mydb/enroll:
FOREIGN KEY (`sectno`)
REFERENCES `mydb`.`section` (`sectno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
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.
因此,我认为可以断定是sectno键及其引用的表给我带来了麻烦。
所以这是我的“注册”表(不允许我创建的表)和“部分”表
-- -----------------------------------------------------
-- Table `mydb`.`section`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`section` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`cno` INT NOT NULL COMMENT '',
`sectno` INT NOT NULL COMMENT '',
`pname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`sectno`, `dname`, `cno`) COMMENT '',
INDEX `fk_cno_section_idx` (`cno` ASC) COMMENT '',
INDEX `fk_pname_section_idx` (`pname` ASC) COMMENT '',
CONSTRAINT `fk_dname_section`
FOREIGN KEY (`dname`)
REFERENCES `mydb`.`dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_cno_section`
FOREIGN KEY (`cno`)
REFERENCES `mydb`.`course` (`cno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_pname_section`
FOREIGN KEY (`pname`)
REFERENCES `mydb`.`prof` (`pname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`enroll`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`enroll` (
`sid` INT NOT NULL COMMENT '',
`grade` DOUBLE NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
`cno` INT NOT NULL COMMENT '',
`sectno` INT NOT NULL COMMENT '',
PRIMARY KEY (`sid`, `dname`, `cno`, `sectno`) COMMENT '',
INDEX `fk_dname_enroll_idx` (`dname` ASC) COMMENT '',
INDEX `fk_cno_enroll_idx` (`cno` ASC) COMMENT '',
INDEX `fk_sectno_enroll_idx` (`sectno` ASC) COMMENT '',
CONSTRAINT `fk_sid_enroll`
FOREIGN KEY (`sid`)
REFERENCES `mydb`.`student` (`sid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_dname_enroll`
FOREIGN KEY (`dname`)
REFERENCES `mydb`.`dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_cno_enroll`
FOREIGN KEY (`cno`)
REFERENCES `mydb`.`course` (`cno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_sectno_enroll`
FOREIGN KEY (`sectno`)
REFERENCES `mydb`.`section` (`sectno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
我正在使用MySQL工作台前向工程,所以我实际上并没有自己输入代码。我已经检查过并仔细检查过并三重检查过,所有数据类型似乎都匹配,因此问题一定出在此错误上:“无法在引用表中找到索引,其中引用列显示为第一列。” 我不能完全确定我是否100%理解它,但是我认为这意味着被引用的FK必须位于被引用的表的第一列中。因此,在截面表中,我喜欢..声明
`sectno` INT NOT NULL COMMENT '',`
在所有其他列之前,因此它是“第一”,仍然没有任何内容。我已经花了一个又一个小时又一个小时一个小时地工作,而我……精神上已经筋疲力尽了。有人请帮忙
编辑:这是其余的表:
-MySQL Workbench正向工程
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,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`student` (
`sid` INT NOT NULL COMMENT '',
`sname` VARCHAR(45) NULL COMMENT '',
`sex` VARCHAR(10) NULL COMMENT '',
`age` INT NULL COMMENT '',
`year` INT NULL COMMENT '',
`gpa` DOUBLE NULL COMMENT '',
PRIMARY KEY (`sid`) COMMENT '')
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`dept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`dept` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`numphds` INT NULL COMMENT '',
PRIMARY KEY (`dname`) COMMENT '')
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`prof`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`prof` (
`pname` VARCHAR(45) NOT NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`pname`) COMMENT '',
INDEX `fk_dname_prof_idx` (`dname` ASC) COMMENT '',
CONSTRAINT `fk_dname_prof`
FOREIGN KEY (`dname`)
REFERENCES `mydb`.`dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`course`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`course` (
`cno` INT NOT NULL COMMENT '',
`cname` VARCHAR(45) NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`cno`, `dname`) COMMENT '',
INDEX `pk_dname_course_idx` (`dname` ASC) COMMENT '',
CONSTRAINT `pk_dname_course`
FOREIGN KEY (`dname`)
REFERENCES `mydb`.`dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`major`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`major` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`sid` INT NOT NULL COMMENT '',
PRIMARY KEY (`dname`, `sid`) COMMENT '',
INDEX `fk_sid_major_idx` (`sid` ASC) COMMENT '',
CONSTRAINT `fk_dname_major`
FOREIGN KEY (`dname`)
REFERENCES `mydb`.`dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_sid_major`
FOREIGN KEY (`sid`)
REFERENCES `mydb`.`student` (`sid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
该脚本一直运行。如果不是这样,则将顶部的两行更改为不同的架构名称,然后在未comment
激活任何节的情况下运行它。
由于这个原因,我目前无法找到参考,但是我在这里偶然发现了它。
需要明确的是,在某些接口中,一个comment
部分会引发错误。
不用说,它们的运行顺序很重要。
您所有的FK条件看起来都不错。该数据类型和标志是从优秀和匹配引用来引用的表。并且在引用的表中,它们在这些列上都具有最左侧的索引。
create schema asdf789;
use asdf789;
CREATE TABLE IF NOT EXISTS `section` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`cno` INT NOT NULL COMMENT '',
`sectno` INT NOT NULL COMMENT '',
`pname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`sectno`, `dname`, `cno`) COMMENT '',
INDEX `fk_cno_section_idx` (`cno` ASC) COMMENT '',
INDEX `fk_pname_section_idx` (`pname` ASC) COMMENT ''
)ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `student` (
`sid` INT NOT NULL COMMENT '',
`sname` VARCHAR(45) NULL COMMENT '',
`sex` VARCHAR(10) NULL COMMENT '',
`age` INT NULL COMMENT '',
`year` INT NULL COMMENT '',
`gpa` DOUBLE NULL COMMENT '',
PRIMARY KEY (`sid`) COMMENT '')
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`dept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dept` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`numphds` INT NULL COMMENT '',
PRIMARY KEY (`dname`) COMMENT '')
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`prof`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `prof` (
`pname` VARCHAR(45) NOT NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`pname`) COMMENT '',
INDEX `fk_dname_prof_idx` (`dname` ASC) COMMENT '',
CONSTRAINT `fk_dname_prof`
FOREIGN KEY (`dname`)
REFERENCES `dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`course`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `course` (
`cno` INT NOT NULL COMMENT '',
`cname` VARCHAR(45) NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`cno`, `dname`) COMMENT '',
INDEX `pk_dname_course_idx` (`dname` ASC) COMMENT '',
CONSTRAINT `pk_dname_course`
FOREIGN KEY (`dname`)
REFERENCES `dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`major`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `major` (
`dname` VARCHAR(45) NOT NULL COMMENT '',
`sid` INT NOT NULL COMMENT '',
PRIMARY KEY (`dname`, `sid`) COMMENT '',
INDEX `fk_sid_major_idx` (`sid` ASC) COMMENT '',
CONSTRAINT `fk_dname_major`
FOREIGN KEY (`dname`)
REFERENCES `dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_sid_major`
FOREIGN KEY (`sid`)
REFERENCES `student` (`sid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `enroll` (
`sid` INT NOT NULL COMMENT '',
`grade` DOUBLE NULL COMMENT '',
`dname` VARCHAR(45) NOT NULL COMMENT '',
`cno` INT NOT NULL COMMENT '',
`sectno` INT NOT NULL COMMENT '',
PRIMARY KEY (`sid`, `dname`, `cno`, `sectno`) COMMENT '',
INDEX `fk_dname_enroll_idx` (`dname` ASC) COMMENT '',
INDEX `fk_cno_enroll_idx` (`cno` ASC) COMMENT '',
INDEX `fk_sectno_enroll_idx` (`sectno` ASC) COMMENT '',
CONSTRAINT `fk_sid_enroll`
FOREIGN KEY (`sid`)
REFERENCES `student` (`sid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_dname_enroll`
FOREIGN KEY (`dname`)
REFERENCES `dept` (`dname`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_cno_enroll`
FOREIGN KEY (`cno`)
REFERENCES `course` (`cno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_sectno_enroll`
FOREIGN KEY (`sectno`)
REFERENCES `section` (`sectno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句