CODEWITHSUNDEEP
javamysqlsqljdbc
MrPencil
MrPencil

Reputation: 964

Syntax error creating a foreign key

I am trying to create the following tables design but I am getting this error below how can I set the foreign key for the stops table in the arrivaltimes table?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY REFERENCES stops(stop_id) )' at line 4 

stt.execute("CREATE TABLE IF NOT EXISTS stops"
        + "(stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
        + " name varchar(30) NOT NULL, " + " route INT(11) NOT NULL, "
        + " lat double(10,6) NOT NULL, "
        + " longi double(10,6)NOT NULL) ");

stt.execute("CREATE TABLE IF NOT EXISTS arrivaltimes(id INT(11) NOT NULL PRIMARY KEY,"
        + " weekday VARCHAR(20) NOT NULL,"
        + "arrivaltime time NOT NULL,"
        + " stop_id INT FOREIGN KEY REFERENCES stops(stop_id) )" );

Upvotes: 1

Views: 658

Answers (3)

Asaph
Asaph

Reputation: 162851

Change

stop_id INT FOREIGN KEY REFERENCES stops(stop_id)

to

stop_id INT, FOREIGN KEY fk_stop_id(stop_id) REFERENCES stops(stop_id)

Upvotes: 1

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

If you look at the MySQL CREATE TABLE syntax, then you have the choice between:

An inline definition (note the absence of FOREIGN KEY)

stop_id INT REFERENCES stops(stop_id)

and an explicit definition:

stop_id INT,
FOREIGN KEY (stop_id) REFERENCES stops(stop_id)

or better (with named constraint):

stop_id INT,
CONSTRAINT fk_arrivaltimes_stops FOREIGN KEY (stop_id) REFERENCES stops(stop_id)

Upvotes: 0

Radu Lemnaru
Radu Lemnaru

Reputation: 9

I have updated the query, please take note of the syntax of the FOREIGN KEY, where you had an error. Cheers!

stt.execute("CREATE TABLE IF NOT EXISTS stops"
        + "(stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
        + " name varchar(30) NOT NULL, " + " route INT(11) NOT NULL, "
        + " lat double(10,6) NOT NULL, "
        + " longi double(10,6)NOT NULL) ");



stt.execute("CREATE TABLE IF NOT EXISTS arrivaltimes(id INT(11) NOT NULL PRIMARY KEY,"
                            + " weekday VARCHAR(20) NOT NULL,"
                            + "arrivaltime time NOT NULL,"
                            + " FOREIGN KEY (stop_id) REFERENCES stops(stop_id) )" );

Upvotes: 0

Related Questions