Search code examples
mysqlspringhibernateconstraintsdelete-row

How to delete row from table without delete a row from referenced table?

When I try to delete a row from table company_catalog I want to not delete the data from the referenced table store_catalog. Constraint in table store_catalog with on delete no action, but server return exception:

org.hibernate.exception.ConstraintViolationException: could not execute statement
....
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`practick`.`store_catalog`, CONSTRAINT `store_catalog_ibfk_2` FOREIGN KEY (`idGoodsOnFirm`) REFERENCES `company_catalog` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 87 more

DAOImpl:

    @Override
public void deleteGoods(CatalogCompany catalogCompany) throws SQLException {
    Session session = null;
    Transaction tx = null;
    try {
        session = this.sessionFactory.openSession();
        tx = session.beginTransaction();
        session.delete(catalogCompany);
        tx.commit();
    } catch (Exception e) {
        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    } finally {
        if (session != null && session.isOpen())
            session.close();
    }
}

enter image description here

Solution

  • The delete on action you are referring to is for cascading in the child table. You cannot delete data from a child table that is referencing a parent table. You will break ACID principles, such as data integrity, considering that you will be left with orphaned data. You would first have to de-normalize the table (break normalization) in order to delete the row(s) you want from the parent table.

    Please refer to this link: https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavio