Cause: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`essh`.`t_base_user_role`, CONSTRAINT `FK2A5097C7712445C5` FOREIGN KEY (`USER_ID`) REFERENCES `t_base_user` (`ID`)) ; ]; Cannot delete or update a parent row: a foreign key constraint fails (`essh`.`t_base_user_role`, CONSTRAINT `FK2A5097C7712445C5` FOREIGN KEY (`USER_ID`) REFERENCES `t_base_user` (`ID`)); nested exception is java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`essh`.`t_base_user_role`, CONSTRAINT `FK2A5097C7712445C5` FOREIGN KEY (`USER_ID`) REFERENCES `t_base_user` (`ID`))
时间: 2023-07-13 20:23:57 浏览: 183
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row:
这个异常是由于在删除t_base_user表中的数据时,该用户的ID在t_base_user_role表中被引用,导致级联删除失败。要解决这个问题,有以下几种方案:
1. 设置外键约束的级联删除选项为CASCADE,这样在删除t_base_user表中的数据时,t_base_user_role表中与该用户相关的数据也会被自动删除。修改sql语句如下:
```
<delete id="deleteUser" parameterType="pojo.Menu">
delete from t_base_user where ID = #{ID};
</delete>
```
2. 先删除t_base_user_role表中与该用户相关的数据,再删除t_base_user表中的数据。修改sql语句如下:
```
<delete id="deleteUserRole" parameterType="pojo.Menu">
delete from t_base_user_role where USER_ID = #{ID};
</delete>
<delete id="deleteUser" parameterType="pojo.Menu">
delete from t_base_user where ID = #{ID};
</delete>
```
3. 修改外键约束的级联操作选项为SET NULL,这样在删除t_base_user表中的数据时,t_base_user_role表中与该用户相关的数据的USER_ID字段会被设置为NULL。需要在t_base_user_role表中添加一个USER_ID字段的默认值为NULL。修改sql语句如下:
```
<alter table="t_base_user_role">
<change column="USER_ID">
<set null/>
</change>
</alter>
<delete id="deleteUser" parameterType="pojo.Menu">
delete from t_base_user where ID = #{ID};
</delete>
```
以上三种方案,根据实际情况选择合适的方案即可。
阅读全文