ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails MySQL

Question

I've looked everywhere for a possible solution and haven't been able to find anything.

Here are my tables:

CREATE TABLE IF NOT EXISTS students(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(40) NOT NULL,
middle_name VARCHAR(20),
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
password CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (student_id),
UNIQUE(email));

And,

CREATE TABLE IF NOT EXISTS subjects(
subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(20) NOT NULL,
level_of_entry VARCHAR(12) NOT NULL,
exam_board VARCHAR(12) NOT NULL,
PRIMARY KEY (subject_id),
UNIQUE(subject_id));

Finally,

CREATE TABLE IF NOT EXISTS entries (
entry_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(entry_id),
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
date_of_exam DATETIME NOT NULL,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id));

I need to enter 5 records into the table entries using foreign keys from the other two tables. This is the code I used to enter records into entries:

INSERT INTO entries 
VALUES (NULL,   NULL,   NULL,   '2010-04-19'),
(NULL,  NULL,   NULL,   '2015-06-17'),
(NULL,  NULL,   NULL,   '2011-07-21'),
(NULL,  NULL,   NULL,   '2009-01-12'),
(NULL,  NULL,   NULL,   '2016-11-16');

This error message is returned:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`exams`.`entries`, CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)) 

Any ideas or suggestions would be appreciated.

Answer 1

You have declared referenced columns within entries table to be NOT NULL yet you're trying to insert a NULL value for them. This won't work, obviously.

Also, since your entry_id has an AUTO_INCREMENT you should be omitting this column in INSERT statements since it fills itself and also can't be NULL.

It is a good practice to include columns you're inserting the values into within the statement. Given the above your insert could look like:

INSERT INTO entries ( student_id, subject_id, date_of_exam )
VALUES 
(?, ?, '2010-04-19'),
(?, ?, '2015-06-17'),
(?, ?, '2011-07-21'),
(?, ?, '2009-01-12'),
(?, ?, '2016-11-16');

You have to replace ? signs with values that correspond to those stored within the referenced tables. If tables subjects and students do not have those values then prior to adding a row in entries you should add rows to those tables to be able to reference them later.

One last note - when doing an INSERT you don't have to specify all the columns your table owns. This would mean that if a column is nullable it would put a NULL value in it unless of course you have a default assigned to it.

Answer 2

You have to insert records on students and subjects table first before inserting any thing on entries table because their primary key is being used as foreign key in entries table.

Here either you have to remove foreign contraints or ignore foreign check using following command.

SET FOREIGN_KEY_CHECKS = 0;