http://www.muxuanli.com/lmx/
有两张表,结构如下:
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
分享到:
相关推荐
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 这是因为你尝试删除的表中的字段被用作了其他表的外键,因此在删除这个表(父表)之前必须先删除具有外键的表(子表)...
student表的主键是selectedcourse表的外键,当需要删除student表内的一行数据时,必须在selectedcourse表内设置该外键在删除时的属性方能实现删除。 当我想删除student表内的某行数据时,也需要删除selectedcourse...
(1452, 'Cannot add or update a child row: a foreign key constraint fails (`mxonline`.`django_admin_log`, CONSTRAINT `django_admin_log_user_id_c564eba6_fk_auth_user_id` FOREIGN KEY (`user_id`) ...
这篇文章主要涉及到在数据创建表时,遇到ERROR 1215 (HY000): Cannot add foreign key constraint 问题方面的内容,对于在数据创建表时,遇到同样问题感兴趣的同学可以参考一下。 一、问题的提出 创建两个表: ...
Cannot delete or update a parent row: a foreign key constraint fails (...) 这可能是MySQL在InnoDB中设置了foreign key关联,造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。 SET ...
aforeignkeyconstraintfails (...) 这是因为MySQL中设置了foreign key关联,造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。 禁用外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=...
constraint 外键名 foreign key(字段) references 主表(字段) on delete 级联动作 on update 级联动作 3、级联动作 1、cascade :删除、更新同步(被参考字段) 2、restrict :不让主表更新、删除 3、set null...
阅读此书以了解根本原因:SQL FOREIGN KEY Constraint [^]
The foreign key in a table T1 _____ the same _____ as the corresponding primary key in table T2. must have, name need not have, name must have, domain (a) I, II, and III (b) I and II (c) ...
foreign key(AID) references dbo.A(AID) on delete cascade on update cascade go alter table dbo.C add constraint FK_C_B_BID foreign key(BID) references dbo.B(BID) on delete cascade on update cascade go ...
在MySQL中,InnoDB引擎类型的表支持了外键约束。外键的使用条件:1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但...外键的定义语法: 代码如下:[CONSTRAINT symbol] FOREIGN KEY [i
添加级联操作 语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; 2. 分类: 1. 级联更新:ON UPDATE CASCADE 2...
"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'a3b6420a-6724-11ea-b2a3-d773d1d6999f' for key 'callId'\nThe SQL being executed was: INSERT INTO `ly_call` (`call_id`, `mobile`, ...
课程设计—仓库设备管理系统.doc 创建基本表的SQL代码: create table Equipment ( ...alter table Stock add constraint Eno3 foreign key(Eno) references Equipment(Eno) on update cascade on delete cascade
like 'M%':M开头的 like '_a%':第二个字符是a的 like '%a%'所有含a的 (“_”表示一个任意字符;“%”表示任意多个任意字符。) 单引号里面的内容,大小写敏感。单引号用来限定字符串, 如果将值与串类型的列...
Grammatical theory From transformational grammar to constraint-based approaches Stefan Müller 2017新书,欢迎对形式语言学,自然语言处理等领域感兴趣读者下载。
Processing Rule : Short-Circuit Constraint (Allowed=Not Allowed) (On the board ),(On the board ) Rule Violations :0 Processing Rule : Broken-Net Constraint ( (On the board ) ) Rule Violations :0 ...
For example, suppose a transaction scanned a page using an S lock and then subsequently decided to perform a row level update. The row would obtain an X lock, but now the page would require an IX ...
利用Python+Gurobi编写代码,复现文章:Solving two-stage robust optimization problems using a column-and- constraint generation method。