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

badCoder

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

BK435

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Delete from one table unless row is referenced in another table

From Dev

How to delete row from table column javafx

From Dev

Delete a row from Parse Table

From Dev

Delete a row from sqlite table

From Dev

Delete row from dynamic table

From Dev

how delete parent row with all child row from other table

From Dev

Delete row from table conditional on a related table

From Dev

prevent soft delete if row is referenced in another table

From Dev

unable to delete row from table using jquery?

From Dev

delete a row from table based on a specific association

From Dev

"On Delete Cascade" if deleting a row from the child table

From Dev

DELETE row from table takes very long

From Dev

Delete the current row from an internal table in a loop

From Dev

Delete selected row from table in mysql

From Dev

Delete multiple table cells from specific row

From Dev

PHP button delete row from table not working

From Dev

delete row from wordpress custom table

From Dev

Delete an entire row from a table, using MySQL

From Dev

delete existing row from data table

From Dev

unable to delete row from table using jquery?

From Dev

ios delete row from table in ViewController

From Dev

delete a row from table based on a specific association

From Dev

Delete selected row from table in mysql

From Dev

I want to delete row from both table

From Dev

To delete a row dynamically from a table using javascript

From Dev

Delete row from database with a button in table (PHP)

From Dev

jQuery onclick delete row from mysql table

From Dev

Delete row from a table after some time

From Dev

How to delete from table then delete what deleted rows referenced? (postgresql)

Related Related

HotTag

Archive