Cannot delete or update a parent row - Java

Question

I teacher is trying to delete a row, which is used by a student. But how can I delete this row anyway? If the teacher wants to delete the lesson it should delete it anyway?

This is the function I have for the delete query:

con = DriverManager.getConnection ("jdbc:mysql://localhost:3307/lessons","root","");
String query = "DELETE FROM lessons WHERE Number= ?";
PreparedStatement pst = con.prepareStatement(query);
pst.setString(1,txtFieldNumber.getText());
pst.executeUpdate();

.

CREATE TABLE UserLogin(
   Number INTEGER,
   UserNumberINTEGER,
   FOREIGN KEY (Number) REFERENCES termin(Number),
   FOREIGN KEY (UserNumber) REFERENCES User(UserNumber)
);

CREATE TABLE lessons(
   Number INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
   LName VARCHAR(20)
);

CREATE TABLE User(
   Name VARCHAR (20),
   UserNUmber INTEGER NOT NULL PRIMARY KEY
);

Where is the declaration for the `lessons` table and which one is the parent table you are referring to? — Joakim Danielson, Jul 11 '18 at 15:07
I am so confused, sorry it was my fault. My parent tables are lessons and user. And I am trying to delete the number from lessons, and after deleting this the program should delete it from UserLogin (Number) as well. — Lia, Jul 11 '18 at 15:24

tourist · Answer 1 · 2018-07-12T16:00:23.203

1

You can't use setString when the underlying column is int

Assuming your txtFieldNumber.getText() returns a number in String format, Try the following

pst.setInt(1,Integer.parseInt(txtFieldNumber.getText()));

Update:

Based on your question edit, looks like you are first trying to delete primary key in lessons which is being referenced in UserLogin table. This is the reason you're facing the error.

To overcome this, you may want to first delete in UserLogin table and then delete the corresponding rows in lessons table.

String query = "DELETE FROM UserLogin WHERE Number= ?";
PreparedStatement pst = con.prepareStatement(query);
pst.setInt(1,Integer.parseInt(txtFieldNumber.getText()));
pst.executeUpdate();

String query2 = "DELETE FROM lessons WHERE Number= ?";
pst = con.prepareStatement(query2);
pst.setInt(1,Integer.parseInt(txtFieldNumber.getText()));
pst.executeUpdate();

This should solve your issue

edited Jul 12 '18 at 16:00

answered Jul 10 '18 at 20:57

tourist

4,165
6
25
47

thx. But will this work for the problem with cannot delete update a parent row?? – Lia Jul 10 '18 at 21:02
What do you mean by "cannot delete update a parent row?" Did the solution I've given work? – tourist Jul 10 '18 at 21:03
I am trying to delete a row which is used by the student. But if the teacher wants to delete the row it should delete it for the student anyway. If am trying to delete the row at the moment , I got the message " cannot delete update a parent row" :/ – Lia Jul 10 '18 at 21:15
1

We will need the table structure (columns) for the `lessons` table and the `constraints` that reference the columns on that table before we can know why you are unable to `delete` a particular record. – Edward Jul 10 '18 at 21:25
You first need to `delete` the rows which are being referenced and then `delete` the parent row. I guess you need two `delete` statements to `delete` from both the tables – tourist Jul 10 '18 at 21:51
I agree with @Edward having table description and `constraints` would really help to answer your question better and faster – tourist Jul 10 '18 at 21:56
This is more suited as a comment than an answer. – Joakim Danielson Jul 11 '18 at 15:08

Joakim Danielson · Accepted Answer · 2018-07-12T10:24:06.563

1

You have to perform 2 separate deletes and in the right order using the same value for the Number parameter.

First delete from UserLogin with

DELETE FROM UserLogin WHERE Number = ?

And then use the command you have today

DELETE FROM lessons WHERE Number = ?

If you want to be sure both statements gets executed properly you can use manual commit like this

edited Jul 12 '18 at 10:24

answered Jul 12 '18 at 10:18

Joakim Danielson

43,251
5
22
52

Cannot delete or update a parent row - Java

2 Answers2