Results 1 to 16 of 16
  1. #1
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default SQL Exception when creating table

    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
    Java Code:
    CREATE TABLE Student(StudentID VARCHAR(6),Student_Name VARCHAR(30),Degree_Scheme VARCHAR(40),PRIMARY KEY(StudentID),FOREIGN KEY(StudentID) REFERENCES Registered(StudentID));
    INSERT INTO Student VALUES ('S10345','John Smith','BSc Computer Science');
    INSERT INTO Student VALUES ('S10346','Sian Evans','BSc Computer Science');
    INSERT INTO Student VALUES ('S10347','Sean Crossan','Bsc Elec Engineering');
    INSERT INTO Student VALUES ('S10348','Jamie McDonald','BSc Mathematics');
    CREATE TABLE Modules(ModuleID VARCHAR(5),Module_Name VARCHAR(40),Credits INT(2),PRIMARY KEY(ModuleID), FOREIGN KEY(ModuleID) REFERENCES Registered(ModuleID));
    INSERT INTO Modules VALUES ('CS101','Introduction to Computing','10');
    INSERT INTO Modules VALUES ('CS203','Data Structures and Algorithims','10');
    INSERT INTO Modules VALUES ('CS204','Computer Architecture','10');
    INSERT INTO Modules VALUES ('M101','Mathematics 1','20');
    CREATE TABLE Registered(StudentID VARCHAR(6),ModuleID VARCHAR(5), PRIMARY KEY(StudentID,ModuleID), FOREIGN KEY(StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE, FOREIGN KEY(ModuleID) REFERENCES Modules(ModuleID) ON DELETE CASCADE);
    INSERT INTO Registered VALUES ('S10345','CS101');
    INSERT INTO Registered VALUES ('S10346','CS203');
    INSERT INTO Registered VALUES ('S10346','CS204');
    INSERT INTO Registered VALUES ('S10347','CS204');
    INSERT INTO Registered VALUES ('S10348','M101');
    INSERT INTO Registered VALUES ('S10348','C101');
    CREATE TABLE Staff(StaffID VARCHAR(6), Staff_Name VARCHAR(30),Grade VARCHAR(20),PRIMARY KEY(StaffID), FOREIGN KEY(StaffID) REFERENCES Teaches(StaffID));
    INSERT INTO Staff VALUES ('E10010','Alan Turing','Senior Lecturer');
    INSERT INTO Staff VALUES ('E10011','Tony Hoare','Reader');
    INSERT INTO Staff VALUES ('E10012','Seymour Cray','Lecturer');
    CREATE TABLE Teaches(StaffID VARCHAR(6),ModuleID VARCHAR(5), PRIMARY KEY(StaffID, ModuleID), FOREIGN KEY(StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE, FOREIGN KEY(ModuleID) REFERENCES Modules(ModuleID));
    INSERT INTO Teaches VALUES ('E10010','CS101');
    INSERT INTO Teaches VALUES ('E10011','CS203');
    INSERT INTO Teaches VALUES ('E10012','CS204');
    INSERT INTO Teaches VALUES ('E10010','CS204');
    INSERT INTO Teaches VALUES ('E10011','M101');
    INSERT INTO Teaches VALUES ('E10011','C101');
    error message
    Java Code:
    Exception in thread "main" java.sql.SQLException: Can't create table 'university.student' (errno: 150)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2678)
    	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
    	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
    	at DatabaseGenerator.main(DatabaseGenerator.java:25)
    Line 25 in DatabaseGenerator is just where the sql command is executed.

  2. #2
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Since it was a bit cut off at the end, it's hard to say where the problem is... A suggestion: Remove first the foreign keys, then the keys, and then basically all variables but StudentID from the creation statement and see when it starts working. If it never starts working, then start looking at database privileges (although I think that'd be a different errno and a different error description).

  3. #3
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Although actually, I might've found it... I honestly don't use foreign keys at all, so I'm a bit fuzzy on them, but... I see in the Staff table you try to reference the Teaches table, but the Teaches table isn't created yet at that point.

  4. #4
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    Hi Toll, i did see that i was trying to reference tables that didn't exist, so i have moved the creation of foreign keys to ALTER TABLE statements at the end of the script file.
    I am still getting the same error message though so i will start working my way through your original suggestion.

  5. #5
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Another tip is to println the statement before you execute it, if you don't already. That way, you'll know for sure which statement is causing you problems.

  6. #6
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    Ok, so i thought i would try to execute my script file in mySQL workbench
    The code executes up to this line
    Java Code:
    ALTER TABLE Modules ADD FOREIGN KEY(ModuleID) REFERENCES Registered(ModuleID);
    Where it gives the error message
    Java Code:
    Error Code: 1005. Can't create table 'university.#sql-2ea_9' (errno: 150)
    I assume, seeing as the code runs via workbench, the problem is with the file i use to execute each statement?

  7. #7
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Found in a google: MySQL Error Number 1005 Can’t create table ‘.mydb#sql-328_45.frm’ (errno: 150) | VerySimple

    Especially this note stands out: One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint.

  8. #8
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    Well fixing one problem leads me on to my next. I have added the updated script file i am using which includes the added indexes. The next error message is below.

    Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`university`.`#sql-597_4`, CONSTRAINT `#sql-597_4_ibfk_2` FOREIGN KEY (`ModuleID`) REFERENCES `Modules` (`ModuleID`) ON DELETE CASCADE)

    I have googled the error message, and so far haven't found a clear answer

    Java Code:
    CREATE TABLE Student(StudentID VARCHAR(6),Student_Name VARCHAR(30),Degree_Scheme VARCHAR(40),PRIMARY KEY(StudentID));
    INSERT INTO Student VALUES ('S10345','John Smith','BSc Computer Science');
    INSERT INTO Student VALUES ('S10346','Sian Evans','BSc Computer Science');
    INSERT INTO Student VALUES ('S10347','Sean Crossan','Bsc Elec Engineering');
    INSERT INTO Student VALUES ('S10348','Jamie McDonald','BSc Mathematics');
    CREATE TABLE Modules(ModuleID VARCHAR(5),Module_Name VARCHAR(40),Credits INT(2),PRIMARY KEY(ModuleID));
    INSERT INTO Modules VALUES ('CS101','Introduction to Computing','10');
    INSERT INTO Modules VALUES ('CS203','Data Structures and Algorithims','10');
    INSERT INTO Modules VALUES ('CS204','Computer Architecture','10');
    INSERT INTO Modules VALUES ('M101','Mathematics 1','20');
    CREATE TABLE Registered(StudentID VARCHAR(6),ModuleID VARCHAR(5), PRIMARY KEY(StudentID,ModuleID));
    INSERT INTO Registered VALUES ('S10345','CS101');
    INSERT INTO Registered VALUES ('S10346','CS203');
    INSERT INTO Registered VALUES ('S10346','CS204');
    INSERT INTO Registered VALUES ('S10347','CS204');
    INSERT INTO Registered VALUES ('S10348','M101');
    INSERT INTO Registered VALUES ('S10348','C101');
    CREATE TABLE Staff(StaffID VARCHAR(6), Staff_Name VARCHAR(30),Grade VARCHAR(20),PRIMARY KEY(StaffID));
    INSERT INTO Staff VALUES ('E10010','Alan Turing','Senior Lecturer');
    INSERT INTO Staff VALUES ('E10011','Tony Hoare','Reader');
    INSERT INTO Staff VALUES ('E10012','Seymour Cray','Lecturer');
    CREATE TABLE Teaches(StaffID VARCHAR(6),ModuleID VARCHAR(5), PRIMARY KEY(StaffID, ModuleID));
    INSERT INTO Teaches VALUES ('E10010','CS101');
    INSERT INTO Teaches VALUES ('E10011','CS203');
    INSERT INTO Teaches VALUES ('E10012','CS204');
    INSERT INTO Teaches VALUES ('E10010','CS204');
    INSERT INTO Teaches VALUES ('E10011','M101');
    INSERT INTO Teaches VALUES ('E10011','C101');
    CREATE INDEX StudentI ON Registered(StudentID);
    CREATE INDEX ModuleI ON Registered(ModuleID);
    ALTER TABLE Student ADD FOREIGN KEY(StudentID) REFERENCES Registered(StudentID);
    ALTER TABLE Modules ADD FOREIGN KEY(ModuleID) REFERENCES Registered(ModuleID);
    ALTER TABLE Registered ADD FOREIGN KEY(StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE, ADD FOREIGN KEY(ModuleID) REFERENCES Modules(ModuleID) ON DELETE CASCADE;
    ALTER TABLE Staff ADD FOREIGN KEY(StaffID) REFERENCES Teaches(StaffID);
    ALTER TABLE Teaches ADD FOREIGN KEY(StaffID) REFERENCES Staff(StaffiD) ON DELETE CASCADE, ADD FOREIGN KEY(ModuleID) REFERENCES Modules(ModuleID);

  9. #9
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Which statement does it fail on?

  10. #10
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Oh, I actually think I found the problem. Hmm. Let's try like this... What is the effect of foreign keys? Why are they used?

  11. #11
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    A foreign key is used to enforce a link between data in two tables. Also the error occurs on the statement on line 33

  12. #12
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Right. And a constraint error is when the foreign key wouldn't match up with the available data. Check through the columns involved in the foreign key you're trying to create and see if you can find a problem with the data provided.

  13. #13
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    OK i have found the problem as well. The moduleID values in registered didn't match up with those in the modules table. The error would also happen for the last statement as well so i have adjusted the ModuleID in these tables. I am using test data provided to us so i had assumed it would be correct.
    Thanks for the help!

  14. #14
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Glad to! Oh, and for an added bonus... When all the tables are created and filled according to that file, would you be able to add a new student? Try it and see!

  15. #15
    wdh321 is offline Senior Member
    Join Date
    Aug 2011
    Posts
    116
    Rep Power
    0

    Default Re: SQL Exception when creating table

    Quote Originally Posted by Toll View Post
    Glad to! Oh, and for an added bonus... When all the tables are created and filled according to that file, would you be able to add a new student? Try it and see!
    Is it something to do with studentID being a foreign key for the Registered table?
    I have also managed to successfully execute the sql queries within my java program.

  16. #16
    Toll's Avatar
    Toll is offline Senior Member
    Join Date
    May 2011
    Location
    Sweden
    Posts
    393
    Rep Power
    4

    Default Re: SQL Exception when creating table

    Right! Foreign keys should only point in one direction. So the Registered should have a foreign key, but not Student. If ordered correctly, you should generally not have to use ALTER TABLE to add foreign keys; I won't say you'll never have to, although from the top of my head I can't think of any situation that would require it. In this case, if you create Student and Modules first, you can then add the Registered table with its foreign keys without causing any errors.

Similar Threads

  1. Creating a table
    By Mate de Vita in forum AWT / Swing
    Replies: 15
    Last Post: 04-24-2012, 12:35 AM
  2. Creating a Table with user input
    By JonniBravo in forum Eclipse
    Replies: 1
    Last Post: 09-08-2010, 01:50 PM
  3. Need help creating a table
    By Knizz in forum SWT / JFace
    Replies: 3
    Last Post: 07-18-2009, 04:46 AM
  4. Creating a SWT table with 1,000,000 items
    By Java Tip in forum SWT Tips
    Replies: 0
    Last Post: 07-11-2008, 05:31 PM
  5. regarding to creating table.....
    By daredavil82 in forum New To Java
    Replies: 0
    Last Post: 11-18-2007, 05:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •