can not add or update table

Question

Trying to add a new record to a data table in MySQL fails with an error message:

can not add or update a child row

I can do the same command manually in xampp and it works fine, but not when it runs under my app. here is the Code, starting with the table "users" and then "transactions"

 public void CreateUsersTable(){
    try {
        String SQL = "CREATE TABLE IF NOT EXISTS Users ("
                + "Username varchar(80) NOT NULL,"
                + "Userpassword varchar(80) ,"
                + "User_GSM VARCHAR(30),"
                + "User_Tel_Home VARCHAR(30),"
                + "User_Address Varchar(100), "
                + "User_City Varchar(20), "
                + "User_Position Varchar(100), "
                + "PRIMARY KEY(Username))";

        //con = DBModule.ConnectDataBase.ConnectDataBase_Method();
        statement = con.prepareStatement(SQL);
        statement.executeUpdate();

    } catch (SQLException ex) {
         CustomControls.CustomTools.CustomMsgBox(ex.getMessage());
    }
}

and the second table

  public void CreateTransactionsTable(){
    try {
        String SQL = "CREATE TABLE IF NOT EXISTS Transactions ("
                + "TransactionsNum INT(18) NOT NULL AUTO_INCREMENT,"
                + "TransactionsDate DATE,"
                + "TransactionsAmount float(8), "
                + "TransactionsUsername varchar(80) ,"
                + "PRIMARY KEY(TransactionsNum) , "
                + "FOREIGN KEY(TransactionsUsername) REFERENCES Users(Username) )"; // foregign key is the key in this table to accessed from main calling

        //con = DBModule.ConnectDataBase.ConnectDataBase_Method();
        statement = con.prepareStatement(SQL);
        statement.executeUpdate();
    } catch (SQLException ex) {
          CustomControls.CustomTools.CustomMsgBox(ex.getMessage());
    }
}

and finally, this the statement to update the DB

 String addTRansSQL = "insert into transactions ( TransactionsDate , TransactionsAmount , TransactionsUsername ) " 
            + " values( '" + sqlDate + "' , '" + tramount + "' , ' " + loggeduser + "' )";

Btw: https://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement — luk2302, Oct 15 '18 at 15:00
Check the vaue of `loggeduser` - does it exist in the Users table? Also, using concatenation to build the query is open to SQL Injection attack - use a PrepareStatement to set the query parameter values. — Andrew S, Oct 15 '18 at 15:02
yes , it does exist. Moreover I can do this manually in xampp and it works fine!! — Ahmed Shehata, Oct 15 '18 at 15:08
I *really* hope you're properly hashing those passwords with something like [Bcrypt](https://en.wikipedia.org/wiki/Bcrypt). — tadman, Oct 15 '18 at 15:11
Try and use `VARCHAR(255)` as a default and only restrict that if you have a very compelling reason. For many things, like names and email addresses, shorter fields can cause huge hassles for your users. — tadman, Oct 15 '18 at 15:11
I just tried something by updating the table with only one col. which is the TransactionUsername (FK) and it works. Any hint why is this happeing ? — Ahmed Shehata, Oct 15 '18 at 15:17

score 0 · Answer 1 · answered Oct 15 '18 at 15:20

0

Simply, you're trying to add a transaction row with a user that doesn't exists in the Users table, try these line "SET FOREIGN_KEY_CHECKS=0"

answered Oct 15 '18 at 15:20

Ahmed Abol-Ela

13
5

it does exist in the users table, and i tested that manually in xampp and it works fine. – Ahmed Shehata Oct 15 '18 at 15:22
just try the following SET FOREIGN_KEY_CHECKS=0; insert into transactions ( TransactionsDate , TransactionsAmount , TransactionsUsername ) values ("2010-10-10","50","Mohamed"); SET FOREIGN_KEY_CHECKS=1; – Ahmed Abol-Ela Oct 15 '18 at 15:27
I think the issue that the userName column in Users table is not NULL, try to make it not NULL also in the trans table – Ahmed Abol-Ela Oct 15 '18 at 15:31
I did changed it to NULL also in transactions table, but still the same error. – Ahmed Shehata Oct 15 '18 at 15:40
Have you tried this line: "SET FOREIGN_KEY_CHECKS=0;" ?? – Ahmed Abol-Ela Oct 15 '18 at 15:46
I have just created a method to toggle the key_checks On and Off, i did made it off before executing the update statement, and same error. – Ahmed Shehata Oct 15 '18 at 15:53
Make sure to select your database before execute the key check, Use urDatabaseName; SET FOREIGN_KEY_CHECKS=0; – Ahmed Abol-Ela Oct 15 '18 at 15:59
I do use the normal connect to database and execute a normal update to set the FK ON and OFF, if not so, would you mind providing me with the right coding. thanks – Ahmed Shehata Oct 15 '18 at 16:05
Ok, just try to execute the following and revert with the result String addTRansSQL = "Use urDatabaseName; SET FOREIGN_KEY_CHECKS=0; insert into transactions ( TransactionsDate , TransactionsAmount , TransactionsUsername ) " + " values( '" + sqlDate + "' , '" + tramount + "' , ' " + loggeduser + "' ); SET FOREIGN_KEY_CHECKS=1;"; – Ahmed Abol-Ela Oct 15 '18 at 16:10
I have removed the sqlDate , tramount from the sql statement and left only the TransactionsUsername which is the FK and it worked, so I don't figure it out what is happeing!!! – Ahmed Shehata Oct 15 '18 at 16:25

can not add or update table

1 Answers1