1 错误描述:
场景:hibernate 自动建立非主键多对一关联时候
ERROR: HHH000389: Unsuccessful: alter table ORDERS_NOPK1 add index FK_590egrftni5wpw63w6fcc1atx (CUSTOMER_ID), add constraint FK_590egrftni5wpw63w6fcc1atx foreign key (CUSTOMER_ID) references CUSTOMERS_NOPK1 (cy_id)
ERROR: Cannot add foreign key constraint
Customer.java
package cn.com.pojo.n21.both.notPK;
import java.util.HashSet;
import java.util.Set;
public class Customer {
private Integer id;
// 外键对应列
private Integer cyId;
private String customerName;
private Set<Order> orders = new HashSet<>();
public Customer() {
super();
// TODO Auto-generated constructor stub
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public Set<Order> getOrders() {
return orders;
}
public void setOrders(Set<Order> orders) {
this.orders = orders;
}
public Integer getCyId() {
return cyId;
}
public void setCyId(Integer cyId) {
this.cyId = cyId;
}
@Override
public String toString() {
return "Customer [id=" + id + ", cyId=" + cyId + ", customerName="
+ customerName + ", orders=" + orders + "]";
}
}
Customer.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-9-17 15:48:32 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping package="cn.com.pojo.n21.both.notPK">
<class name="Customer" table="CUSTOMERS_NOPK1">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="native" />
</id>
<property name="customerName" type="java.lang.String">
<column name="CUSTOMER_NAME" />
</property>
<property name="cyId" type="java.lang.Integer">
<!--正确的:<column name="cy_id" not-null="true" unique="true"/> -->
<column name="cy_id"/> <!--报错代码 -->
</property>
<!-- 映射 1 对多 集合属性 -->
<set name="orders" table="ORDERS" cascade="delete" inverse="true">
<!-- key: 指定多端表中的外键列名字 -->
<key column="CUSTOMER_ID" property-ref="cyId"></key>
<!-- class指定映射java类 -->
<one-to-many class="Order"/>
</set>
</class>
</hibernate-mapping>
Order.java
package cn.com.pojo.n21.both.notPK;
public class Order {
private Integer orderId;
private String orderName;
private Customer customer;
public Order() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Order [orderId=" + orderId + ", orderName=" + orderName
+ "]";
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
}
Order.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-9-17 15:48:32 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping package="cn.com.pojo.n21.both.notPK">
<class name="Order" table="ORDERS_NOPK1">
<id name="orderId" type="java.lang.Integer">
<column name="ORDER_ID" />
<generator class="native" />
</id>
<property name="orderName" type="java.lang.String">
<column name="ORDER_NAME" />
</property>
<!-- 在多端设置外键 -->
<many-to-one name="customer" class="Customer" property-ref="cyId">
<column name="CUSTOMER_ID"/>
</many-to-one>
</class>
</hibernate-mapping>
2 分析原因:
原因可能出现在外键与其对应字段不符合上面
2.1来源于网络:
(1)外键对应的字段数据类型不一致
排除: show create table table_name
(2)两张表的存储引擎不一致
排除: show table status from db_name where name=’table_name’;
(3)设置外键时“删除时”设置为“SET NULL”
排除:
2.2补充:以上原因在这种情况下不太可能出现,因为都是hibernate自动建表,此时可能是下面原因
(4)外键对应字段必须设置 非空并且唯一
3 解决问题:
发现外键对应列没有设置非空或者唯一约束,应该加上约束条件
<property name="cyId" type="java.lang.Integer">
<column name="cy_id" not-null="true" unique="true"/>
</property>