I'm getting an ERROR 1452 (23000) in mysql

Question

I'm experiencing this error when I try to insert date into my table. Here is my full error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (university.course, CONSTRAINT course_ibfk_2 FOREIGN KEY (prerequisite) REFERENCES course (course_id))

Here are my tables:

CREATE TABLE department (
    dept_id INT(1) UNIQUE,
    dept_name VARCHAR(50) NOT NULL,
    budget INT(10) NOT NULL,
    PRIMARY KEY (dept_id))ENGINE=INNODB;



CREATE TABLE term (
    term_id VARCHAR(50) UNIQUE,
    term_desc VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY (term_id))ENGINE=INNODB;



CREATE TABLE major (
    major_id VARCHAR(3) UNIQUE,
    major_desc VARCHAR(50) NOT NULL,
    PRIMARY KEY (major_id))ENGINE=INNODB;



CREATE TABLE location (
    room_id INT(2) UNIQUE,
    building VARCHAR(50) NOT NULL,
    room_no INT(3) NOT NULL,
    capacity INT(2) NOT NULL,
    room_type VARCHAR(1) NOT NULL,
    room_description VARCHAR(50) NOT NULL,
    PRIMARY KEY (room_id))ENGINE=INNODB;

CREATE TABLE course
(
    course_id VARCHAR(6),
    title VARCHAR(30) NOT NULL UNIQUE,
    credits INT(1) NOT NULL,
    dept_id INT(1) NOT NULL AUTO_INCREMENT,
    prerequisite VARCHAR(6),
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_id) REFERENCES department (dept_id),
    FOREIGN KEY (prerequisite) REFERENCES course (course_id)
)
ENGINE=innodb;

CREATE TABLE faculty
(
    faculty_id INT(5),
    name VARCHAR(30) NOT NULL,
    room_id INT(2),
    dept_id INT(1) NOT NULL,
    salary DECIMAL(8,2),
    PRIMARY KEY (faculty_id),
    FOREIGN KEY (room_id) REFERENCES location (room_id),
    FOREIGN KEY (dept_id) REFERENCES department (dept_id)
)
ENGINE=innodb;


CREATE TABLE student
(
    student_id INT(5),
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    street VARCHAR(60),
    city VARCHAR(40),
    state CHAR(2),
    zip VARCHAR(5),
    birth_date DATE,
    major_id VARCHAR(3),
    phone VARCHAR(10),
    student_type ENUM('ugrad','grad'),
    PRIMARY KEY (student_id),
    FOREIGN KEY (major_id) REFERENCES major (major_id)
)
ENGINE=innodb;

CREATE TABLE section
(
    section_id INT(4),
    course_id VARCHAR(6) NOT NULL,
    section_number VARCHAR(2) NOT NULL,
    term_id VARCHAR(4) NOT NULL,
    faculty_id INT(5),
    day VARCHAR(8),
    max_count INT(2),
    start_time TIME,
    end_time TIME,
    room_id INT(2),
    PRIMARY KEY (section_id),
    FOREIGN KEY (course_id) REFERENCES course (course_id),
    FOREIGN KEY (term_id) REFERENCES term (term_id),
    FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id),
    FOREIGN KEY (room_id) REFERENCES location (room_id)
);

CREATE TABLE registration
(
    student_id INT(5),
    section_id INT(4),
    midterm_grade ENUM('A','B','C','D','F','W'),
    final_grade ENUM('A','B','C','D','F','W'),
    PRIMARY KEY (student_id, section_id),
    FOREIGN KEY (student_id) REFERENCES student (student_id),
    FOREIGN KEY (section_id) REFERENCES section (section_id)
)
ENGINE=innodb;

I'm getting an error when I try to input my values into the tables course, section and registration and I can't seem to figure out how to correct the input values. Here are a couple of the input values I'm trying to insert into the course, section and registration table:

INSERT INTO course (course_id, title, credits, dept_id, prerequisite)
VALUES ('AC101', 'Accounting', '3', '3', 'EN100')

INSERT INTO section (section_id, course_id, section, term_id, faculty_id, day, max_count, start_time, end_time, room_id)
VALUES ('1101', 'CIS265', '01', 'WN13', '63395', 'MW', '30', '09:00', '10:30', '13')

INSERT INTO registration (student_id, section_id, midterm_grade, final_grade)
VALUES ('24746', '1102', 'B', 'B')

Anytime I try to input values into these tables I get the ERROR 1452 (23000). Can someone please help me understand where I'm wrong or how I should input my values?

Answer 1

 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`university`.`course`, CONSTRAINT `course_ibfk_2` 
 FOREIGN KEY (`prerequisite`) REFERENCES `course` (`course_id`))

This error is saying that a value being put into the prerequisite column of the course table does not match a row in the course table.

The row with course_id='EN100' has to exist in the table before we add a row that has prerequisite='EN100'.

---

Insert these rows:

INSERT INTO department (dept_id, dept_name, budget) VALUES 
('1', 'Language Arts', NULL);

INSERT INTO course (course_id, title, credits, dept_id, prerequisite) VALUES 
('EN100', 'English Composition', '3', '1', NULL) ;

INSERT INTO department (dept_id, dept_name, budget) VALUES 
('3', 'Business', NULL);

Before we insert this row:

INSERT INTO course (course_id, title, credits, dept_id, prerequisite) VALUES 
('AC101', 'Accounting', '3', '3', 'EN100') ;