title: Delete foreign key constraint fails
date: 2018-08-02 21:59:06
tags: database
---
When I was learning hibernate at that time, I just learned one to many, many to many related operations. It happens that there is an item just in time, and the structure of the tables should be sorted out. As the saying goes, we need to use it when we learn, so we can configure the structure of these tables with cascade relations. Unexpectedly, there was a small pit for myself. A few days ago, in a post, I saw others say, as few as possible with some ORM constraints, and foreign key constraints of the database. At that time, I did not think so. Unexpectedly, I encountered this problem. Perhaps the constraints of people who are not familiar with database operations really do not deserve too much. But nature has its advantages.
The following errors were reported today when deleting data from a table:
09:55:49.144 [http-nio-8080-exec-6] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Cannot delete or update a parent row: a foreign key constraint fails (`checkin`.`right_umenu`, CONSTRAINT `FKnmg8itd642tdyn6qh1q42h60r` FOREIGN KEY (`menu_detailPid`) REFERENCES `menu_detail` (`id`))
09:55:49.151 [http-nio-8080-exec-6] ERROR org.hibernate.internal.SessionImpl - HHH000346: Error during managed flush [could not execute statement]
org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3198)
.......
The results obtained after searching on the Internet are the same as I think, which is caused by the problem of foreign keys. (of course, the information in the wrong report is written in foreign key. On the internet, it is said that foreign keys are forcibly generated between tables, and when deleting operations, the database will check the relationship between tables, which makes it impossible to delete them.
Solution:
SET foreign_key_checks = 0; // First set the foreign key constraint check to close drop table table1; // Delete tables, and if you want to delete views, the same is true SET foreign_key_checks = 1; // Open foreign key constraint checking to maintain table structure integrity
Principle:
There is a foreign_key_checks in MySQL's environment variable, which is the default configuration item for checking foreign keys. If it is set to 0, it means that foreign key constraints are not checked. View the value of foreign_key_checks:
show VARIABLES like "foreign%";
However, however. Even if I turn off the foreign key constraint check, I will report an error when I open the check. That's what I have to do.
However, when I tried to change the deletion item of foreign key from RESTRICT to CASCADE, I could not only delete it without affecting the execution of my other method, but also execute the method I wanted to write more conveniently.
This is an option for database to define foreign keys. You can know that there are four words that can be followed by update and delete.
no action ， set null ， set default ，cascade .
No action means no action. set null means that the corresponding field is set to null in the foreign key table set default means set to default (restrict) Cascade means cascade operation, that is, if the primary key table is updated by the reference field, the foreign key table is also updated, the records in the primary key table are deleted, and the changes in the foreign key table are deleted accordingly.
So when I set it up like this, the cascade deletion I want to do is also executed, and a lot of things in the database have to be repaired.