How to delete a row from a MySQL table when there is a foreign key constraint?

Question

I tried to delete the an entry from a table through http://localhost/phpmyadmin. It returned the following error :

"Cannot delete or update a parent row: a foreign key constraint fails (press.prodstock, CONSTRAINT fk_prodstock_prodlist1 FOREIGN KEY (item_code) REFERENCES prodlist (id) ON DELETE NO ACTION ON UPDATE NO ACTION) "

I wrote a JAVA code to delete it from NetBeans :

 private void loginBut46ActionPerformed(java.awt.event.ActionEvent evt) {                                           
    if (prodlist.getSelectedRowCount() == 0) {

            JOptionPane.showMessageDialog(rootPane, "Select Raw to View!");

        } else {
            int r = prodlist.getSelectedRow();
            String id = prodlist.getValueAt(r,0).toString();
            String item_code = prodlist.getValueAt(r,1).toString();
        try {
           DB.DB.statement("delete from prodlist where id = '"+id+"' ");
            DB.DB.statement("delete from prodstock where item_code='"+item_code+"'");
        } catch (Exception ex) {
            Logger.getLogger(Stock.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    }

You have to delete the referencing data before the referenced data. — Uueerdo, May 09 '18 at 20:54
DB.DB.statement("delete from prodstock where item_code='"+item_code+"'"); DB.DB.statement("delete from prodlist where id = '"+id+"' "); Is this the correct order @Uueerdo? — Dilanka Dias, May 09 '18 at 20:55
You can not delete the parent because it has still some referenced data in some other table. You have to first delete the referenced data and then delete the parent data. You can also set `ON DELETE CASCADE` on the referenced table, that would delete the referenced data when parent data is deleted. More can be found on this link. http://www.mysqltutorial.org/mysql-on-delete-cascade/ — SK. Fazlee Rabby, May 09 '18 at 20:58
@DilankaDias yeah, just swapping them around from how they are in the question should be enough. — Uueerdo, May 09 '18 at 21:27

score 3 · Accepted Answer · answered May 10 '18 at 18:12

When deleting data that is referenced by other data (and that reference enforced by a foreign key constraint), you must delete the referencing data first or update the referencing data to no longer reference it (or have the constraint set up to do one or the other automatically for you).

You could remove the constraint, do what you need to do, and add the constraint back (which can be necessary in some self-referential, or circular reference cases); but this should be a last resort as you are basically telling the database "let me break the data contract for a while"; and risks not being able to add the constraint back if something went wrong. It also removes the constraint globally, allowing any client to break the contract during that time.

score 2 · Answer 2 · answered May 09 '18 at 21:17

2

For deleting this row, first, you need to delete foreign key constraint. You cannot delete primary key data unit you have deleted the foreign key constraint. Follow this link: Cannot delete or update a parent row: a foreign key constraint fails

answered May 09 '18 at 21:17

Ali Hasan

21
1

How to delete a row from a MySQL table when there is a foreign key constraint?

2 Answers2