Search code examples
javamysqljdbcphpmyadminjavadb

Cant insert into intermediate table from code but when executing from phpmyadmin in MySQL it inserts it properly

I recently needed to add an intermediate table into my database. It is all in innoDB. I use Java on the software, i've never had issues excecuting SQL commands. After inserting the table and setting it up alongside with the code i realiced i could insert data into it from java. It always gave the same error 

    Cannot add or update a child row: a foreign key constraint fails 
    (`jjeventoscorev3`.`event_category_link`, CONSTRAINT 
    `event_category_link_ibfk_1` FOREIGN KEY (`event_category_id`) 
    REFERENCES `event_category` (`event_category_id`))

I checked the query:

    insert into event_category_link (event_link_id, event_id, 
    event_category_id) values (NULL,118,1)

I then tried to execute it on phpmyadmin. id did it successfully under the same query. I dont really know how to debugg it. The code is the same as ive used for other intermediate tables and its nothing out of the extraordinary.

This is the code i used on a table succesfully on java:

 try{con = (Connection) DriverManager.getConnection("jdbc:mysql://"+home.credentials[0],home.credentials[1],home.credentials[2]);
            String query3 = " insert into client_event (client_id, event_id)"
            + " values (?, ?)";/*query para insertar en la tabla intermedia*/
            PreparedStatement preparedStmt3 = con.prepareStatement(query3);
            if(existing == false){/*si existe se manda el ids[0]*/
                preparedStmt3.setInt (1, ids[0]); //cliente
            }
            else{
                preparedStmt3.setInt (1, id_existing); /*se manda el id existente*/
            }
            preparedStmt3.setInt (2, ids[1]); //evento
            preparedStmt3.execute();
            con.close();
            JOptionPane.showMessageDialog(new JFrame(), "Evento añadido");
            pnlone1.continue_doc = true; /*se cambia el valor de continue_doc en el pnlone para poder generar el archivo*/
        }
        catch (Exception e){
            System.err.println("Got an exception! in 3");
            System.err.println(e.getMessage());
            continuar = false; /*se pasa a falso para no continuar con la operación*/
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(new JFrame(), "Error: verificar datos");
        }

This is the code i use on the table I'm getting the error:

      if(continuar == true){
        System.out.println("Iniciando tabla de eventos conectados");
        try{con = (Connection) DriverManager.getConnection("jdbc:mysql://"+home.credentials[0],home.credentials[1],home.credentials[2]);
            String query4 = " insert into event_category_link (event_link_id, event_id, event_category_id)"
            + " values (NULL,"+ids[1]+","+eventlist.get(0)+")";/*query para insertar en la tabla intermedia*/

            //INSERT INTO `event_category_link` (`event_link_id`, `event_id`, `event_category_id`) VALUES (NULL, '105', '27');
            PreparedStatement preparedStmt4 = con.prepareStatement(query4);
            System.out.println(preparedStmt4);
            preparedStmt4.execute();
            con.close();
            JOptionPane.showMessageDialog(new JFrame(), "Relacion añadida");
        }catch (Exception e){
            System.err.println("Got an exception! in 4");
            System.err.println(e.getMessage());
            JOptionPane.showMessageDialog(new JFrame(), "Error: verificar datos");
        }

Here you can see how PhpMyAdmin executes it perfectly

enter image description here I would really appreciate your help. Thanks

Solution

  • The error is telling you that the event_category_id you are trying to insert does not exist in the moment you are trying to insert it. You should take this as a fact, and check your code how that can be the case. Maybe try to run a query directly before your query4 that checks if the value "eventlist.get(0)" is actually in the table. Then go backwards and check where that value comes from.

    There are several reasons why you may be able to execute it later in phpmyadmin (and they should not delude you from the fact that the value is really missing, but may help narrow down where to look):

    • The missing value exists by now. This can e.g. happen if the code to insert the missing event_category_id runs after your failed insert (and you continued the app after the foreign key error occured). Or the insert to event_category happened in a different transaction than the insert to event_category_link and was not yet committed and thus available (as you seem to open a new connection for every query). Or your code accidently deletes/updates event_category_id, but due to the error, a transaction might be rolled back, so it's available again when you do your test on phpmyadmin.
    • That's not actually the (exact) same query. It's suspicious that you use value in your application code, and VALUE and ''-quotes around your values in the query you tested. Try actual copy & paste. Thousands of man-years have been spent staring at code that was exactly the same, but wasn't. In your case, e.g. '01' and 01 could behave completely different.
    • You get a variation of this if the error occurs at a different place, e.g. you insert into that table in another part of your code. Your exception message includes the "4"-marker, so I assume you were able to identify the right code, but it might be worth verifying. You can e.g. get this error indirectly by doing something to a different table that has a trigger that violates that foreign key constraint.
    • You may be running your query on a different database. You seem to have 4 revisions of your database, maybe your application (or this specific query) connects to a different one than phpmyadmin.
    • You can disable foreign key checks in phpmyadmin (which would allow you to bypass the check). While my spanish(?) is a bit rusty, it should be the last checkbox in your image, and it's checked, so that's probably not it.