mysql基础(六) 表删除时,报"Connot delete or update a parent row:a foreign key constraint fails"解决方案二
来源:互联网 发布:软件研发费用 编辑:程序博客网 时间:2024/05/25 19:59
一、有两张表,结构如下:
t_item: t_bid: id int id int name varchar name varchar item_id int
其中表t_item的主键id是表t_bid的item_id字段的外键。那么在这种情况下,如果删除表t_item中的记录,并且该记录中的id主键被t_bid中的item_id字段所引用,就会抛出如下异常:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`test/t_bid`, CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `t_item `(`id`))二、解决方法:级联删除,即在删除t_item表中的记录时同时删除t_bid表中的相关记录
(1) 增加外键约束时声明级联删除,即:
alter table t_bid add constraint fk_id foreign key(id) references key(id) on delete cascade;(2) 使用触发器:在删除t_item表中记录之前先删除与之相关的t_bid表中的记录。
触发器代码(MySQL):
delimiter // create trigger tri_delete before delete on t_item for each row begin delete from t_bid where id = old.id; end //Hibernate中的解决方案:
这个问题在Hibernate中相对容易解决,只需设置cascade = “delete”即可。此时观察发出的sql语句:
Hibernate: select item0_.id as id0_0_, item0_.name as name0_0_ from t_item item0_ where item0_.id=? Hibernate: select bids0_.item_id as item3_1_, bids0_.id as id1_, bids0_.id as id1_0_, bids0_.price as price1_0_, bids0_.item_id as item3_1_0_ from t_bid bids0_ where bids0_.item_id=? Hibernate: update t_bid set item_id=null where item_id=? Hibernate: delete from t_bid where id=? Hibernate: delete from t_bid where id=? Hibernate: delete from t_item where id=?发现在删除t_bid表中记录之前会先将它的item_id字段值设置为null,但如果我们在映射文件中设置item_id字段不能为null,即设置Bid.hbm.xml文件为:
<many-to-one name="item" column="item_id" class="po.Item" not-null="true"/>注意不能在Item.hbm.xml文件中进行如下设置(即在key元素中指定not-null="true"):
<set name="bids" cascade="all"> <key column="item_id" not-null="true"/> <one-to-many class="po.Bid"/> </set>这样会抛出"Repeated column in mapping for entity"异常http://www.iteye.com/topic/786535
如果我们指定item_id字段值不能为null,那么在删除时会抛出如下异常:
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update··· Caused by: java.sql.BatchUpdateException: Data truncation: Column set to default value;
NULL supplied to NOT NULL column 'item_id' at row 1 •••此时的解决方法是设置inverse="true",这在Hibernate文档中有相应的描述:
Very Important Note: If the <key> column of a <one-to-many> association is declared NOT NULL, Hibernate may cause constraint violations when it creates or updates the association. To prevent this problem, you must use a bidirectional association with the many valued end (the set or bag) marked as inverse="true".观察此时发出的sql语句:
Hibernate: select item0_.id as id1_0_, item0_.name as name1_0_ from t_item item0_ where item0_.id=? Hibernate: select bids0_.item_id as item3_1_, bids0_.id as id1_, bids0_.id as id0_0_, bids0_.amount as amount0_0_, bids0_.item_id as item3_0_0_ from t_bid bids0_ where bids0_.item_id=? Hibernate: delete from t_bid where id=? Hibernate: delete from t_bid where id=? Hibernate: delete from t_item where id=?没有发出update语句。关于inverse="true"的理解:http://lijiejava.iteye.com/blog/776587
0 0
- mysql基础(六) 表删除时,报"Connot delete or update a parent row:a foreign key constraint fails"解决方案二
- mysql基础(五) Mysql删除表时,报"Connot delete or update a parent row:a foreign key constraint fails"解决方案
- MySQL表中有外键约束时级联删除报错annot delete or update a parent row: a foreign key constraint fails
- 级联删除报Cannot delete or update a parent row: a foreign key constraint fails解决方法
- linux系统mysql删除一行报错:Cannot delete or update a parent row: a foreign key constraint fails
- 表删除时Cannot delete or update a parent row: a foreign key constraint fails 异常处理
- 表删除时 Cannot delete or update a parent row: a foreign key constraint fails 异常处理
- 表删除时 Cannot delete or update a parent row: a foreign key constraint fails 异常处理
- 表删除时 Cannot delete or update a parent row: a foreign key constraint fails 异常处理
- [MySQL]MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- hibernate 级联删除报Cannot delete or update a parent row: a foreign key constraint fails异常
- MYSQL "Cannot delete or update a parent row: a foreign key constraint fails "
- MySQL错误号1451-Cannot delete or update a parent row: a foreign key constraint fails
- MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
- JS 自制换页函数
- Android性能优化(一)
- IOS开发历程(一)
- MySQL第三方复制工具
- PyPI中国镜像知多少和国内的python用户如何使用pypi镜像源
- mysql基础(六) 表删除时,报"Connot delete or update a parent row:a foreign key constraint fails"解决方案二
- 浅谈 Linux 内核开发之网络设备驱动
- 继承
- 【Java EE (Struts2 + Spring + Hibernate)开发】 :Hibernate(二)之【数据过滤|事务控制|二级缓存和查询缓存|事件机制】
- Java(Android)线程池
- Nginx 安装
- LAMP LAMP环境搭建【rpm】+ 实例应用
- C#的Task多线程编程
- 文件和参数一起上传到服务器