Error executing Create table statement in Java?

Question

I am trying to create 2 tables in MySql using Java JDBC,the first one runs fine but I get an error when I execute the second create table command. The problem looks like in the Foreign key definition not sure what's missing ?

Error :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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(UniqueBusID)
REFERENCES BUS_3_CLEARBROOK_UFV_GOLINE_TO_UFV_BusDetai' at line 1

Code

String table_UniqueBusNameTimings = BusDetails+"_BusTimings";
String timings= "Timings";
String dayofweek = "DayOfWeek";
String FuniqueBusID = "UniqueBusID";

String table_UniqueBusNameDetails = BusDetails+"_BusDetails";
String PuniqueBusID = "UniqueBusID";
String StopNames = "StopNames" ;

String sql1 = "CREATE TABLE "+table_UniqueBusNameDetails+
   "(UniqueBusID VARCHAR(255) not NULL, " + 
   " StopNames VARCHAR(1000), " +
   " PRIMARY KEY ( UniqueBusID ))";

String sql2 = "CREATE TABLE "+table_UniqueBusNameTimings+
   "(Timings VARCHAR(255) , " + 
   " DayOfWeek VARCHAR(25), " +
   " UniqueBusID VARCHAR(255) "+
   " FOREIGN KEY(UniqueBusID)  REFERENCES "+table_UniqueBusNameDetails+"(UniqueBusID))";

stmt.executeUpdate(sql1);
stmt.executeUpdate(sql2);

Please take more time to format your code in future - use the preview to see how the post will look to everyone else before you submit it. Note that Stack Overflow doesn't play terribly nicely with tabs - use spaces instead. — Jon Skeet
– Jon Skeet, Commented Feb 20, 2014 at 7:16
Looks like you're missing a comma after UniqueBusID VARCHAR(255) — Jon Skeet
– Jon Skeet, Commented Feb 20, 2014 at 7:17

Neels · Accepted Answer · 2014-02-20 07:16:53Z

1

You have a comma missing after UniqueBusID VARCHAR(255). Just change it to UniqueBusID VARCHAR(255), and your code shall be fine.

For details on syntax related to CREATE TABLE with FOREIGN KEY, you can explore this page: http://www.w3schools.com/sql/sql_foreignkey.asp

answered Feb 20, 2014 at 7:16

Neels

2,5436 gold badges36 silver badges41 bronze badges

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

2 Comments

dev_marshell08 Over a year ago

thank you ... never knew it was such a silly mistake

Neels Over a year ago

Welcome.. Sometimes you just need another pair of eyes. :)

Suzon · Accepted Answer · 2014-02-20 07:24:19Z

1

We should use CREATE TABLE IF NOT EXISTS... then if we are having foreign key relation then we should use SET FOREIGN_KEY_CHECKS=0; Finally we will execute a batch what having all the statement and then commit.

answered Feb 20, 2014 at 7:24

Suzon

7591 gold badge8 silver badges21 bronze badges

1 Comment

dev_marshell08 Over a year ago

Thanks for the Not Exists part.

Zafar Malik · Accepted Answer · 2014-02-20 07:39:02Z

1

Every attribute like column, index, foreign key etc. should be seprated by comma ',' in table creation syntax. so here a comma missing after UniqueBusID VARCHAR(255). It will be fine after adding comma here.

@Suzon: We should not set foreign_key_checks=0 specially during creating table or adding any constraint at it will skip any constraint checking by mysql and create major issue in database. We can use it only we are sure its impact for example we want to delete some rows from master table even child table contains corresponding rows and we are fine with this etc.

answered Feb 20, 2014 at 7:39

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

Error executing Create table statement in Java?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related