Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Exception when creating table

 
tom davies
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom,
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.
 
tom davies
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
tom davies
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All sql code now executes.
I will now need to check it in my java program and see if it executes from within there.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic