hibernate4.x自动建表时候创建外键失败ERROR: HHH000389: Unsuccessful: alter table ORDERS_NOPK1 add index

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>