MySQL - Insert statement fails when entering into a table with only foreign keys

Question

So my table looks like this (from the "Show Create Table <table>" query) :

CREATE TABLE stars_in_movies( star_id int NOT NULL, movie_id int NOT NULL, KEY 'star_id' ('star_id'), KEY 'movie_id' ('movie_id'), CONSTRAINT 'stars_in_movies_ibfk_1' FOREIGN KEY('star_id') REFERENCES 'stars'('id'), CONSTRAINT 'stars_in_movies_ibfk_2' FOREIGN KEY('movie_id') REFERENCES 'movies'('id'), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I am trying to simply enter a new entry into this table as so:

INSERT INTO stars_in_movies (907010, 834410);

And I get an Error as follows:

ERROR 1064 (4200): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to user near '907010, 834410)' at line 1

Now I know that this error is just simply a syntax error, but why am I receiving the error for such a simple INSERT query? Everywhere online suggest that this would be a valid statement, especially if the two entry fields are ACTUAL ids from the other two referenced tables.

EDIT:

Okay so for this trivial example I was simply missing the VALUES keyword. To add onto the question, how come this query returns the same error?

INSERT INTO stars_in_movies VALUES (693109, m.id) SELECT m.id FROM movies m WHERE m.title='Inception';

Re: EDIT, you can't combine INSERT ... VALUES and INSERT ... SELECT — Uueerdo
– Uueerdo, Commented May 6, 2015 at 19:10

spencer7593 · Accepted Answer · 2015-05-06 18:28:51Z

3

Seems like you are missing the VALUES keyword.

INSERT INTO stars_in_movies (star_id, movie_id) VALUES (907010, 834410) ^^^^^^

answered May 6, 2015 at 18:28

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

LeoVannini Over a year ago

Okay that was incredibly trivial, then how come this question here suggest I can get away with the INSERT without the VALUES keyword?

Rahul Tripathi Over a year ago

@LeoVannini:- Correct me if I am wrong but how is that link linked to this one?

LeoVannini Over a year ago

@RahulTripathi I apologize, wrong link

Rahul Tripathi Over a year ago

@LeoVannini:- Yes that is differnt. If you are using select statement to add values then you dont have to add VALUES keyword.

LeoVannini Over a year ago

@RahulTripathi can you check the recent edit and explain then why my query, following the linked page's format, is throwing a syntax error as well?

|

Rahul Tripathi · Accepted Answer · 2015-05-06 18:45:04Z

Try this:

INSERT INTO stars_in_movies values (907010, 834410);

You are missing the keyword values.

SQL FIDDLE DEMO

EDIT:

After you comment, you also have one more option to insert into the table like this

INSERT INTO stars_in_movies select 907010, 834410;

EDIT:

This query wont work

INSERT INTO stars_in_movies VALUES (693109, m.id) SELECT m.id FROM movies m WHERE m.title='Inception';

You need to specify the particular value in the VALUES. In

INSERT INTO stars_in_movies VALUES (693109, m.id)

m.id is not defined and hence it is unknown to the analyzer. So you are getting the error.

You are probably looking for this:

INSERT INTO stars_in_movies SELECT 693109, m.id FROM movies m WHERE m.title='Inception';

YES! thank you so much rahul, that last example is perfect! Thank you so much again, especially for the explanation as to why it wasn't working.

Collectives™ on Stack Overflow

MySQL - Insert statement fails when entering into a table with only foreign keys

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related