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, May 06 '15 at 19:10

score 3 · Answer 1 · answered May 06 '15 at 18:28

3

Seems like you are missing the VALUES keyword.

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

answered May 06 '15 at 18:28

spencer7593

106,611
15
112
140

Okay that was incredibly trivial, then how come this question [here](http://stackoverflow.com/questions/5391344/insert-with-select) suggest I can get away with the INSERT without the VALUES keyword? – LeoVannini May 06 '15 at 18:33
@LeoVannini:- Correct me if I am wrong but how is that [link](http://stackoverflow.com/questions/591222/cannot-connect-to-sql-server-remotely-or-locally-after-reboot/591344#591344) linked to this one? – Rahul Tripathi May 06 '15 at 18:35
@RahulTripathi I apologize, wrong [link](http://stackoverflow.com/questions/5391344/insert-with-select) – LeoVannini May 06 '15 at 18:37
@LeoVannini:- Yes that is differnt. If you are using select statement to add values then you dont have to add VALUES keyword. – Rahul Tripathi May 06 '15 at 18:38
@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? – LeoVannini May 06 '15 at 18:40
@LeoVannini:- Tried to explain that in my answer. Please check! – Rahul Tripathi May 06 '15 at 18:45

Rahul Tripathi · Accepted Answer · 2015-05-06T18:45:04.180

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. — LeoVannini, May 06 '15 at 18:46

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

2 Answers2