MySQL consecutive INSERT statements syntax error

Question

I have a 2 INSERT statements:

INSERT INTO Class (class_name, teacher_id) VALUES ('Math 7A', 000001);

INSERT INTO Class_Student(class_id, student_id) VALUES (1, 000004);

When I inputted each statement one by one, there was no syntax error. But if I put 2 statements together, it returns a syntax error.

You have an error in your SQL syntax; it seems the error is around: 'INSERT INTO Class_Student(class_id, student_id) VALUES (1, 000004)' at line 5

What is in the middle of these 2 statements that causes the syntax error?

PS: Here are my related tables:

 -- `User` Table
CREATE TABLE `User` (
    user_id int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
    username varchar(50) NOT NULL,
    `password` varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    is_teacher boolean NOT NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB;

-- `Class` Tables
CREATE TABLE Class (
    class_id int NOT NULL AUTO_INCREMENT,
    class_name varchar(20) NOT NULL, 
    teacher_id int(6) unsigned NOT NULL,
    PRIMARY KEY (class_id),
    FOREIGN KEY (teacher_id) REFERENCES `User`(user_id)
) ENGINE=InnoDB;

CREATE TABLE Class_Student (
    class_id int NOT NULL,
    student_id int(6) unsigned NOT NULL,
    PRIMARY KEY (class_id, student_id),
    FOREIGN KEY (class_id) REFERENCES Class(class_id),
    FOREIGN KEY (student_id) REFERENCES User(user_id)
) ENGINE=InnoDB;

That's not a mysql error so what else are you using? ALSO you will get a FK error if users is not populated before the 2 inserts and perhaps whatever else you are using is misinterpreting the error. — P.Salmon, May 19 '22 at 09:44
Are you trying to execute two statements in a single call? Most query interfaces do not support multi-query by default. Execute each INSERT in a separate call. — Bill Karwin, May 19 '22 at 16:51

Vetos · Answer 1 · 2022-05-19T10:01:10.077

0

You can't just make an Insert when you have FOREIGN KEY (teacher_id) REFERENCES User(user_id); You have to have teacher_id before to use it as foreign key in User table.

So you are trying to add a row to your Class table for which no matching row (teacher_id) is present in User table.

I've created your tables and tried to make a query. So I've got another error. So your problem is not syntax error.

Here your problem: Can't insert values into table, foreign key constraint keeps failing

Also here error number I've received from testing your queries: ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

edited May 19 '22 at 10:01

answered May 19 '22 at 09:08

Vetos

96
2
11

op has mentioned a syntax error, not a constraint error. – Rinkesh P May 19 '22 at 09:19
I have already populated the User table, including the user_id. When I copied each INSERT statement to run one by one, everything works fine. But if I copied both statements together, it returned the syntax error. – Thomas D May 19 '22 at 09:21
I've made the same tables and the same query. Here is an result: `MySQL said: #1452 - Cannot add or update a child row: a foreign key constraint fails (test2.class_student, CONSTRAINT class_student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (class_id))` – Vetos May 19 '22 at 09:21
@ThomasD It doesn't work even 1 by 1. – Vetos May 19 '22 at 09:23
Here exactly that error: https://stackoverflow.com/questions/21659691/error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails – Vetos May 19 '22 at 09:25
@RinkeshP he could has different type of error because he definitely has data in his tables. But I don't. – Vetos May 19 '22 at 09:33
1

@ThomasD hope it helps you ^_^ – Vetos May 19 '22 at 09:43
1

@Vetos I think it is because I used the online SQL syntax checker. I set up phpmyadmin locally and it works fine. Thanks anyway. – Thomas D May 19 '22 at 09:44

MySQL consecutive INSERT statements syntax error

1 Answers1