Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes SQL Exception when creating table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Exception when creating table" Watch "SQL Exception when creating table" New topic

SQL Exception when creating table

tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
I have a script file with all my commands ready to execute. I am using a small java program to read and execute each line.
I get an sql exception error which you can see below.
I have also used the below link to see if i can find the problem but have had no luck.
Eliacom- MySQL Errno 150, Errno 121, and other Foreign Key Errors. Complete List of Causes and Solutions

script file

error message

Line 25 in DatabaseGenerator is just where the sql command is executed.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

You have an ordering problem. The Students table has a foreign key on the Registered table. But the Registered table hasn't been created yet. And you can't move Registered up because it has a foreign key on the students table. (You probably don't need the relationship in both directions.)

THe way to solve this is to create the table without the foreign key first. Then after the Registered table is defined, use an alter statement to add the foreign key.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
Thank you Jeanne, i will have another look at how i can order the creation better and which tables will need to be altered after creation
chris webster

Joined: Mar 01, 2009
Posts: 2288

In general, I'd recommend you build your SQL file and test executing it via your SQL interpreter first. This means you can trap and fix any errors in your SQL, before you add the extra layer of complexity by processing the SQL command file via Java.

No more Blub for me, thank you, Vicar.
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
Ok, so i thought i would try to execute my script file in mySQL workbench
The code executes up to this line

Where it gives the error message

I assume, seeing as the code runs via workbench, the problem is with the file i use to execute each statement?
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
All sql code now executes.
I will now need to check it in my java program and see if it executes from within there.
I agree. Here's the link:
subject: SQL Exception when creating table
jQuery in Action, 3rd edition