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

我不是女神ヾ 2022-06-06 12:45 132阅读 0赞

**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">
    
    <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"/>  
            </property>
    
            
            <set name="orders" table="ORDERS" cascade="delete" inverse="true">
                
                <key column="CUSTOMER_ID" property-ref="cyId"></key>
                
                <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

**2 分析原因：**

原因可能出现在外键与其对应字段不符合上面  
2.1来源于网络：  
（1）外键对应的字段数据类型不一致  
排除： show create table table\_name

（2）两张表的存储引擎不一致  
排除： show table status from db\_name where name=’table\_name’;  
![这里写图片描述][SouthEast]

（3）设置外键时“删除时”设置为“SET NULL”  
排除：  
![这里写图片描述][SouthEast 1]

2.2补充：以上原因在这种情况下不太可能出现，因为都是hibernate自动建表，此时可能是下面原因  
（4）外键对应字段必须设置 非空并且唯一

**3 解决问题：**

发现外键对应列没有设置非空或者唯一约束，应该加上约束条件

![这里写图片描述][SouthEast 2]

[SouthEast]: /images/20220606/7e99d289858f4770a0b10e2060f3c0b4.png
[SouthEast 1]: /images/20220606/b00cebbda3e0472bba15b5d8127310c9.png
[SouthEast 2]: /images/20220606/6a43e6035682470197041ed2256c9dc4.png