如何解决无法添加或更新子行的错误：外键约束失败？

xdnvmnnf  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(446)

我用mysql workbench创建了一个数据库并添加了一些用户，但是当我尝试在表上插入时 resolved_test 我得到这个错误：

Cannot add or update a child row: a foreign key constraint fails (`titanx_e-learning`.`resolved_test`, CONSTRAINT `fk_resolved_test_student1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

我检查是否试图引用不存在的 student_id 或者 test_id 但这并不是问题所在，所以idk我还能做些什么来思考生产环境来解决这个问题。
p、 s:这正是我正在尝试执行的sql代码：

INSERT INTO resolved_test (student_id, test_id, title, response) VALUES (`student_id` = 1, `test_id` = 1, `title` = 'esta es una pregunta de desarrollo', `response` = 'asdasdasd');

p、 s.2:我用以下结构创建了数据库：

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `lastName` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `rol` VARCHAR(45) NOT NULL,
  `createdAt` DATETIME NULL,
  `lastLogin` DATETIME NULL,
  `emailVerified` VARCHAR(45) NULL DEFAULT 'false',
  `state` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
ENGINE = InnoDB
COMMENT = '     ';

-- -----------------------------------------------------
-- Table `mydb`.`administrator`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`administrator` (
  `administrator_id` INT NOT NULL AUTO_INCREMENT,
  `user_user_id` INT NOT NULL,
  PRIMARY KEY (`administrator_id`),
  INDEX `fk_administrator_user_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_administrator_user`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `mydb`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '             ';

-- -----------------------------------------------------
-- Table `mydb`.`school`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`school` (
  `school_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`school_id`),
  UNIQUE INDEX `school_id_UNIQUE` (`school_id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`teacher`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teacher` (
  `teacher_id` INT NOT NULL AUTO_INCREMENT,
  `user_user_id` INT NOT NULL,
  `school_school_id` INT NOT NULL,
  PRIMARY KEY (`teacher_id`, `school_school_id`),
  INDEX `fk_teacher_user1_idx` (`user_user_id` ASC),
  INDEX `fk_teacher_school1_idx` (`school_school_id` ASC),
  UNIQUE INDEX `teacher_id_UNIQUE` (`teacher_id` ASC),
  CONSTRAINT `fk_teacher_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `mydb`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_teacher_school1`
    FOREIGN KEY (`school_school_id`)
    REFERENCES `mydb`.`school` (`school_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '     ';

-- -----------------------------------------------------
-- Table `mydb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`student` (
  `student_id` INT NOT NULL AUTO_INCREMENT,
  `user_user_id` INT NOT NULL,
  `school_school_id` INT NOT NULL,
  PRIMARY KEY (`student_id`, `school_school_id`),
  INDEX `fk_student_user1_idx` (`user_user_id` ASC),
  UNIQUE INDEX `student_id_UNIQUE` (`student_id` ASC),
  INDEX `fk_student_school1_idx` (`school_school_id` ASC),
  CONSTRAINT `fk_student_user1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `mydb`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student_school1`
    FOREIGN KEY (`school_school_id`)
    REFERENCES `mydb`.`school` (`school_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';

-- -----------------------------------------------------
-- Table `mydb`.`course`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`course` (
  `course_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `img_url` VARCHAR(255) NOT NULL,
  `teacher_teacher_id` INT NOT NULL,
  `teacher_school_school_id` INT NOT NULL,
  `school_id` INT NOT NULL,
  PRIMARY KEY (`course_id`, `teacher_teacher_id`, `teacher_school_school_id`),
  UNIQUE INDEX `course_id_UNIQUE` (`course_id` ASC),
  INDEX `fk_course_teacher1_idx` (`teacher_teacher_id` ASC, `teacher_school_school_id` ASC),
  INDEX `fk_course_school1_idx` (`school_id` ASC),
  CONSTRAINT `fk_course_teacher1`
    FOREIGN KEY (`teacher_teacher_id` , `teacher_school_school_id`)
    REFERENCES `mydb`.`teacher` (`teacher_id` , `school_school_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_course_school1`
    FOREIGN KEY (`school_id`)
    REFERENCES `mydb`.`school` (`school_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`unit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`unit` (
  `unit_id` INT NOT NULL AUTO_INCREMENT,
  `number` INT NULL,
  `title` VARCHAR(100) NULL,
  `description` VARCHAR(1000) NULL,
  `state` VARCHAR(45) NULL,
  `course_course_id` INT NOT NULL,
  PRIMARY KEY (`unit_id`, `course_course_id`),
  UNIQUE INDEX `unit_id_UNIQUE` (`unit_id` ASC),
  INDEX `fk_unit_course1_idx` (`course_course_id` ASC),
  CONSTRAINT `fk_unit_course1`
    FOREIGN KEY (`course_course_id`)
    REFERENCES `mydb`.`course` (`course_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`lesson`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`lesson` (
  `lesson_id` INT NOT NULL AUTO_INCREMENT,
  `number` INT NULL,
  `title` VARCHAR(100) NULL,
  `unit_unit_id` INT NOT NULL,
  `unit_course_course_id` INT NOT NULL,
  PRIMARY KEY (`lesson_id`, `unit_unit_id`, `unit_course_course_id`),
  UNIQUE INDEX `lesson_id_UNIQUE` (`lesson_id` ASC),
  INDEX `fk_lesson_unit1_idx` (`unit_unit_id` ASC, `unit_course_course_id` ASC),
  CONSTRAINT `fk_lesson_unit1`
    FOREIGN KEY (`unit_unit_id` , `unit_course_course_id`)
    REFERENCES `mydb`.`unit` (`unit_id` , `course_course_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test` (
  `test_id` INT NOT NULL AUTO_INCREMENT,
  `state` VARCHAR(45) NULL,
  `start` DATETIME NULL,
  `finish` DATETIME NULL,
  `delivery_date` DATETIME NULL,
  `lesson_lesson_id` INT NOT NULL,
  `lesson_unit_unit_id` INT NOT NULL,
  `lesson_unit_course_course_id` INT NOT NULL,
  PRIMARY KEY (`test_id`),
  UNIQUE INDEX `test_id_UNIQUE` (`test_id` ASC),
  INDEX `fk_test_lesson1_idx` (`lesson_lesson_id` ASC, `lesson_unit_unit_id` ASC, `lesson_unit_course_course_id` ASC),
  CONSTRAINT `fk_test_lesson1`
    FOREIGN KEY (`lesson_lesson_id` , `lesson_unit_unit_id` , `lesson_unit_course_course_id`)
    REFERENCES `mydb`.`lesson` (`lesson_id` , `unit_unit_id` , `unit_course_course_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '                             ';

-- -----------------------------------------------------
-- Table `mydb`.`resolved_test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`resolved_test` (
  `resolved_test_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(1000) NULL,
  `reponse` VARCHAR(2000) NULL,
  `student_id` INT NOT NULL,
  `test_id` INT NOT NULL,
  PRIMARY KEY (`resolved_test_id`),
  UNIQUE INDEX `resolved_test_id_UNIQUE` (`resolved_test_id` ASC),
  INDEX `fk_resolved_test_student1_idx` (`student_id` ASC),
  INDEX `fk_resolved_test_test1_idx` (`test_id` ASC),
  CONSTRAINT `fk_resolved_test_student1`
    FOREIGN KEY (`student_id`)
    REFERENCES `mydb`.`student` (`student_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_resolved_test_test1`
    FOREIGN KEY (`test_id`)
    REFERENCES `mydb`.`test` (`test_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`question` (
  `question_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(1000) NULL,
  `type` VARCHAR(45) NULL,
  `test_test_id` INT NOT NULL,
  PRIMARY KEY (`question_id`, `test_test_id`),
  UNIQUE INDEX `question_id_UNIQUE` (`question_id` ASC),
  INDEX `fk_question_test1_idx` (`test_test_id` ASC),
  CONSTRAINT `fk_question_test1`
    FOREIGN KEY (`test_test_id`)
    REFERENCES `mydb`.`test` (`test_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`answer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`answer` (
  `answer_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(1000) NULL,
  `question_question_id` INT NOT NULL,
  PRIMARY KEY (`answer_id`, `question_question_id`),
  UNIQUE INDEX `answer_id_UNIQUE` (`answer_id` ASC),
  INDEX `fk_answer_question1_idx` (`question_question_id` ASC),
  CONSTRAINT `fk_answer_question1`
    FOREIGN KEY (`question_question_id`)
    REFERENCES `mydb`.`question` (`question_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '         ';

-- -----------------------------------------------------
-- Table `mydb`.`activity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`activity` (
  `activity_id` INT NOT NULL AUTO_INCREMENT,
  `number` INT NULL,
  `title` VARCHAR(1000) NULL,
  `description` VARCHAR(1000) NULL,
  `type` VARCHAR(45) NULL,
  `url` VARCHAR(255) NULL,
  `lesson_lesson_id` INT NOT NULL,
  `lesson_unit_unit_id` INT NOT NULL,
  `lesson_unit_course_course_id` INT NOT NULL,
  PRIMARY KEY (`activity_id`, `lesson_lesson_id`, `lesson_unit_unit_id`, `lesson_unit_course_course_id`),
  UNIQUE INDEX `activity_id_UNIQUE` (`activity_id` ASC),
  INDEX `fk_activity_lesson1_idx` (`lesson_lesson_id` ASC, `lesson_unit_unit_id` ASC, `lesson_unit_course_course_id` ASC),
  CONSTRAINT `fk_activity_lesson1`
    FOREIGN KEY (`lesson_lesson_id` , `lesson_unit_unit_id` , `lesson_unit_course_course_id`)
    REFERENCES `mydb`.`lesson` (`lesson_id` , `unit_unit_id` , `unit_course_course_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`course_has_student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`course_has_student` (
  `course_course_id` INT NOT NULL,
  `student_student_id` INT NOT NULL,
  `student_school_school_id` INT NOT NULL,
  PRIMARY KEY (`course_course_id`, `student_student_id`, `student_school_school_id`),
  INDEX `fk_course_has_student_student1_idx` (`student_student_id` ASC, `student_school_school_id` ASC),
  INDEX `fk_course_has_student_course1_idx` (`course_course_id` ASC),
  CONSTRAINT `fk_course_has_student_course1`
    FOREIGN KEY (`course_course_id`)
    REFERENCES `mydb`.`course` (`course_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_course_has_student_student1`
    FOREIGN KEY (`student_student_id` , `student_school_school_id`)
    REFERENCES `mydb`.`student` (`student_id` , `school_school_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
sqlmysqlmariadbsql-insertforeign-keys

2条答案

按热度按时间
cyej8jka

cyej8jka1#

我会先修好你的衣服 insert 查询，格式不正确。这个 values() 子句只能包含要插入的值，而不能包含列名。
因此：

INSERT INTO resolved_test (student_id, test_id, title, response) 
VALUES (`student_id` = 1, `test_id` = 1, `title` = 'esta es una pregunta de desarrollo', `response` = 'asdasdasd');

应写为：

INSERT INTO resolved_test (student_id, test_id, title, response) 
VALUES (1, 1, 'esta es una pregunta de desarrollo', 'asdasdasd');

然后，需要确保给外键列的值存在于父表中。为了这个 insert 查询要工作，以下两个查询必须返回一行：

select * from student where student_id = 1;    
select * from test    where test_id    = 1;
赞(0）分享  回复(0）举报  2021-07-26
vcirk6k6

vcirk6k62#

您正试图插入到引用主表中数据的表中，而该键在主表中不存在。查看resolver\u test的表定义，您应该检查您尝试为student\u id插入的id是否存在于student表中，以及test\u id的id是否存在于test表中。

赞(0）分享  回复(0）举报  2021-07-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com