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);
}
}
}
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
;;