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

    }  

Answer 1

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.