Home > database >  error to update a child row in database and java
error to update a child row in database and java

Time:09-25

I have an error saving the data in my database in two different tables. but with the same ID_USER however one is a primary key and the other unique.

I need that when creating the user it can automatically generate the ID_USER for the other table and save its password. How do I rescue the ID_USER from the previous table to insert it in another? since when I run it I get an error

"Cannot add or update a child row: a foreign key constraint fails (tests``private, CONSTRAINT private_ibfk_1 FOREIGN KEY (ID_USER) REFERENCES user (ID_USER) ON DELETE CASCADE ON UPDATE CASCADE)"

is this possible to do?

 private void insertarActionPerformed(java.awt.event.ActionEvent evt) {                                         

    int val = 0;
    String user, pass = "";

    user = jTextField_user.getText().trim();
    pass = jTextField_pass.getText().trim();

    if (user.equals("")) {
        val  ;
    }
    if (pass.equals("")) {
        val  ;
    }
    if (val == 0) {
        try {
            Connection cn = Conexion.conectar();
            PreparedStatement pst = cn.prepareStatement(
                    "Insert into user (ID_USER,USER) values (?,?)");

            pst.setInt(1, 0);
            pst.setString(2, user);

            pst = cn.prepareStatement(
                    "Insert into private (ID_USER,PASSWORD) values(?,?)");

            pst.setInt(1, 0);
            pst.setString(2, pass);

            pst.executeUpdate();
            cn.close(); 

            JOptionPane.showMessageDialog(null, "Successful registration");

        } catch (Exception e) {
            System.err.println("Error user"   e);
        }
    } 
} 

DATA BASE

CodePudding user response:

solved . create variable ID. and bring it with a ResultSet the LAST_INSERT_ID ()

private void insertarActionPerformed(java.awt.event.ActionEvent evt) {                                         

    int id = 0, val = 0;
    String user, pass = "";

    user = jTextField_user.getText().trim();
    pass = jTextField_pass.getText().trim();

    if (user.equals("")) {
        val  ;
    }
    if (pass.equals("")) {
        val  ;
    }
    if (val == 0) {

        try {

            Connection cn = Conexion.conectar();
            PreparedStatement pst = cn.prepareStatement(
                    "Insert into user values (?,?)");

            pst.setInt(1, 0);
            pst.setString(2, user);
            pst.executeUpdate();

            ResultSet rs = pst.executeQuery("select ID_USER from user where ID_USER = LAST_INSERT_ID() ");
            if (rs.next()) {
                id = rs.getInt("ID_USER");
            }
            pst = cn.prepareStatement(
                    "Insert into private values(?,?)");
            pst.setInt(1, id);
            pst.setString(2, pass);

            pst.executeUpdate();
            cn.close();
            JOptionPane.showMessageDialog(null, "Successful registration");

        } catch (Exception e) {
            System.err.println("Error user"   e);
        }
    }
}          

CodePudding user response:

    CREATE TRIGGER user
BEFORE INSERT ON private
FOR EACH ROW
BEGIN
  IF new.ID_USER IS NULL THEN
    SET new.ID_USER = ID_USER();
  END IF;
END
;;
  • Related