foreign constraint error - MySQL

Question

I'm working on a database so that students can swap classes.

user being the student.

class being the classes.

has being the classes that the student currently has.

wants being the classes that that the student doesn't have but wants.

CREATE TABLE Class
(
ClassName varchar(255),
Professor_Name varchar(255),
Start_Time varchar(10),
End_Time varchar(10),
Course_Number varchar(20),
Section_Number varchar(20),
Days varchar(10),
PRIMARY KEY(Course_Number,Section_Number));

CREATE TABLE User
(
Email VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
password1 VARCHAR(255),
PRIMARY KEY (Email)
);

CREATE TABLE Has
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCES User (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);

CREATE TABLE Wants
(
Email VARCHAR(255),
Course_Number VARCHAR(20),
Section_Number VARCHAR(20),
PRIMARY KEY (Email, Course_Number, Section_Number),
FOREIGN KEY (Email) REFERENCES User (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)    
);

and put classes into the class table and users into the user table and now im trying to add classes that the students "have" and classes that the students may "want"

I wrote the following query to add a class that a student has with the students email, class course number and class section number

INSERT INTO Has
VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');

but for some reason im getting the following error

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (spr17_fkais001.SEHAS, CONSTRAINT SEHAS_ibfk_1 FOREIGN KEY (Email) REFERENCES SEUSER (Email))

does anybody know why this is? and how I can start adding classes to wants and has?

this error typically caused by non existing foreign key, that means you insert a data that don't have corresponding data on the other table defined in the foreign key. to solve this, you need to observe the order of how you input the data, you should input all the foreign keys first, then input your data. or simply remove the foreign keys references — am05mhz, Apr 02 '18 at 04:54
Possible duplicate of [ERROR 1452: Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/21659691/error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) — Nishant Gupta, Apr 02 '18 at 05:05
i have multiple classes in the class table, and 10 users in the user table, i dont understand what ive done wrong — Abdul Balogun, Apr 02 '18 at 05:06
most probably you are trying to insert an email address to the table ‘has’ but it does not exist in the table ‘user’. Does ‘johnsmith@school.edu’ exist in the ‘user’ table? — Abdullah Nehir, Apr 02 '18 at 05:18

score 2 · Answer 1 · answered Apr 02 '18 at 05:29

I think you trying to insert wrong value, because I apply the query with your tables. it was working fine for me.

see below example

Class Table

mysql> select * from Class;
+-----------+----------------+------------+----------+---------------+----------------+------+
| ClassName | Professor_Name | Start_Time | End_Time | Course_Number | Section_Number | Days |
+-----------+----------------+------------+----------+---------------+----------------+------+
| aaa       | vvvvv          | NULL       | NULL     | bca           | B              | NULL |
| bbb       | dasda          | NULL       | NULL     | bcom          | C              | NULL |
+-----------+----------------+------------+----------+---------------+----------------+------+
2 rows in set (0.01 sec)

User Table

mysql> select * from User;
+--------------+------------+-----------+-----------+
| Email        | first_name | last_name | password1 |
+--------------+------------+-----------+-----------+
| aba@abc.com  | asda       | rew       | NULL      |
| rwew@abc.com | dfg        | rfdg      | NULL      |
+--------------+------------+-----------+-----------+
2 rows in set (0.00 sec)

then trying to add a row in Has table it works fine only.

mysql> insert into Has values("aba@abc.com","bca","B");
Query OK, 1 row affected (0.01 sec)

Has Table

mysql> select * from Has;
+-------------+---------------+----------------+
| Email       | Course_Number | Section_Number |
+-------------+---------------+----------------+
| aba@abc.com | bca           | B              |
+-------------+---------------+----------------+
1 row in set (0.01 sec)

Finally trying with improper value ,it got exception,

mysql> insert into Has values("aba@abcsd.com","bca","B");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysampletest`.`has`, CONSTRAINT `has_ibfk_1` FOREIGN KEY (`Email`) REFERENCES `User` (`Email`))

So kindly trying with valid primary key.

foreign constraint error - MySQL

1 Answers1