Java : JDBC Delete Row Issue

Question

This is GUI program in which when click the button it delete the row.But the problem is it give exception like this

Cannot delete or update a parent row: a foreign key constraint fails (`sakila`.`film_actor`, CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE)

I don't know what does it mean but i want to delete that row is there any way that work for this. This is snap of schema and tables.
Code :

public class GUI extends JFrame {

    public GUI() {
        super("Frame");
        setLayout(new FlowLayout());

        JButton b1 = new JButton("Click Delete Row");
        b1.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {

                try {

                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/cinema", "hussnain", "toot");

                    String query = "delete from sakila.actor where actor_id=3";
                    PreparedStatement pre = conn.prepareStatement(query);

                    pre.executeUpdate();
                    JOptionPane.showMessageDialog(null, "Query Executed");
                } catch (Exception e1) {
                    JOptionPane.showMessageDialog(null, e1.getMessage());

                    e1.printStackTrace();
                }

            }

        });

        add(b1);

        setSize(400, 200);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);
        setVisible(true);

    }

}

Main Method

public class Main {

    public static void main(String[] args) {

        GUI obj = new GUI();


    }

}

I belive that you have an reference to another table, that you need to delete before. Check the table "film_actor" — Petter Friberg, Oct 05 '15 at 14:42
Show your database schema. It appears the row is referenced by another record with a foreign key constraint (meaning it can't be deleted whilst the other exists) — James Wierzba, Oct 05 '15 at 14:42
@JamesWierzba i uploaded a snap of schema but how to do this in java — , Oct 05 '15 at 14:48
This has nothing to do with Swing. This is a problem with your SQL statement. (Swing tag removed). — camickr, Oct 05 '15 at 14:59
Did you intend your foreign key to not be `ON DELETE CASCADE`? You might want to consider adding that. — Mark Rotteveel, Oct 05 '15 at 15:01

score 0 · Answer 1 · answered Oct 05 '15 at 14:43

0

A row in the database exists that references the actor_id you're trying to delete.

Check the data you have in the film_actor table.

answered Oct 05 '15 at 14:43

Mike Palfrey

426
3
8

Fatih Donmez · Accepted Answer · 2015-10-05T15:01:52.500

0

You have foreign key constraint for film_actor table so before you delete from actor table, you should first delete from film_actor table.

Also you can disable foreign key check but it will make data inconsistent;

SET FOREIGN_KEY_CHECKS=0; -- disable
SET FOREIGN_KEY_CHECKS=1; -- re-enable

edited Oct 05 '15 at 15:01

answered Oct 05 '15 at 14:53

Fatih Donmez

4,319
3
33
45

1

Disabling foreign key check sounds like the way to make your data inconsistent. – Mark Rotteveel Oct 05 '15 at 14:59
@MarkRotteveel yes obviously it need to be considered. – Fatih Donmez Oct 05 '15 at 15:00
@FatihDonmez i disable the FOREIGN_KEY_CHECKS now it deleted but is there a way that i can do this in java first it disable than re-enable and then what is proper way to do this – Oct 05 '15 at 15:02
check this http://stackoverflow.com/a/4120517/975816 @JohnDoe but as Mark pointed I would first delete from film_actor and then actor in one transaction. In this way you'll have consistent state of data in your database. – Fatih Donmez Oct 05 '15 at 15:07
@FatihDonmez you mean to say first i delete entire fim_actor table then from actor table – Oct 05 '15 at 15:11
@FatihDonmez what queries i write for all this – Oct 05 '15 at 15:16
not the entire table just the rows reference to actor table. In your case actor_id = 3 – Fatih Donmez Oct 05 '15 at 15:16
@FatihDonmez what query it will be in case of actor_id=3 – Oct 05 '15 at 15:17
delete from fim_actor where actor_id=3 – Fatih Donmez Oct 05 '15 at 15:27

score 0 · Answer 3 · edited May 23 '17 at 11:45

the problem you are having is that the query reports you an error about the presence of a foreign key in the table film_actor related to that actor, now to work this out you could consider 2 options:

1) (inconsistent one) - using SET FOREIGN_KEY_CHECKS=0 and ignoring the rest, this way your information in the database may be inconsistent after an update/deletion, this option will ignore the existence of other references of the entry in other tables and will procede with the deletion but will cause to have other references to point to nothing resulting in inconsistent data

2) (suggested one) - you can manage the way of how you want the database to "represent" the reality meaning that you can consider how the deletion of certain table entries will work and setting how update/delete of those ones will spread among other references of the same object, this means that for example setting

ON DELETE CASCADE

in the table definition will let the deletion of the entry of THAT table to delete other references of the same object from other tables meaning that other entries that just includes that reference will be deleted;

OR

you can use the

ON DELETE SET NULL

which will set the reference field of other entries in other tables to NULL whenever you delete that entry in the main table but if the field is a part of a primary key of another table will cause an error,

i cited you only 2 options you can use for your case but you can check the answer on "how ON DELETE works" here to get an idea about that and see how this can fit in your situation

What is MySQL's default ON DELETE behavior?

Java : JDBC Delete Row Issue

3 Answers3