mysql - 无法添加或更新子行: a foreign key constraint fails - OneToMany

Question

我想创建一个网上商店。我定义了一个用户表和一个订单表，它们之间具有一对多的关系。我想在此表中插入一些内容，但收到此错误:

Cannot add or update a child row: a foreign key constraint fails (restaurant_java.order_food, CONSTRAINT fk_order_food_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE)

以下是如何创建表以及如何定义约束。

用户表:

CREATE TABLE users (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    firstname       VARCHAR(200) NOT NULL,
    lastname        VARCHAR(200) NOT NULL,
    cnp             VARCHAR(13) NOT NULL,
    email           VARCHAR(50) NOT NULL,
    password        VARCHAR(100) NOT NULL,
    phone           VARCHAR(10) NOT NULL,
    iban            VARCHAR(20) NOT NULL,
    address         VARCHAR(200) NOT NULL,
    KEY (id)
);
ALTER TABLE users ADD CONSTRAINT pk_users_id PRIMARY KEY (id);

订购食物:

CREATE TABLE order_food (
    id          INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    user_id     INT(10) UNSIGNED,
    created     datetime DEFAULT NOW(),
    closed      datetime,
    total       float,
    KEY(id)
);
ALTER TABLE order_food ADD CONSTRAINT pk_order_food_id PRIMARY KEY (id);
ALTER TABLE order_food ADD CONSTRAINT fk_order_food_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

我还想补充一点，我尝试使用 hibernate 插入到 mySql 数据库中。我该如何解决这个问题？

编辑:以下是我尝试插入新用户的方法:

session.beginTransaction();

Collection<OrderFoodEntity> orderFoodEntities = new HashSet<>();
OrderFoodEntity orderFoodEntity = new OrderFoodEntity();

orderFoodEntity.setTotal((float) 0);
orderFoodEntities.add(orderFoodEntity);
user.setOrderFoodsById(orderFoodEntities);
user.addOrder();

session.persist(user);
session.getTransaction().commit();

实体在这里:

import javax.persistence.*;
import java.util.Collection;

@Entity
@Table(name = "users", schema = "", catalog = "restaurant_java")
public class UsersEntity {
    private int id;
    private String firstname;
    private String lastname;
    private String cnp;
    private String email;
    private String password;
    private String phone;
    private String iban;
    private String address;
    @OneToMany(mappedBy = "usersByUserId", cascade = {CascadeType.ALL})
    private Collection<OrderFoodEntity> orderFoodsById;

    @Id
    @Column(name = "id", nullable = false, insertable = true, updatable = true)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "firstname", nullable = false, insertable = true, updatable = true, length = 200)
    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    @Basic
    @Column(name = "lastname", nullable = false, insertable = true, updatable = true, length = 200)
    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    @Basic
    @Column(name = "cnp", nullable = false, insertable = true, updatable = true, length = 13)
    public String getCnp() {
        return cnp;
    }

    public void setCnp(String cnp) {
        this.cnp = cnp;
    }

    @Basic
    @Column(name = "email", nullable = false, insertable = true, updatable = true, length = 50)
    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Basic
    @Column(name = "password", nullable = false, insertable = true, updatable = true, length = 100)
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Basic
    @Column(name = "phone", nullable = false, insertable = true, updatable = true, length = 10)
    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Basic
    @Column(name = "iban", nullable = false, insertable = true, updatable = true, length = 20)
    public String getIban() {
        return iban;
    }

    public void setIban(String iban) {
        this.iban = iban;
    }

    @Basic
    @Column(name = "address", nullable = false, insertable = true, updatable = true, length = 200)
    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public void addOrder() {
        for (OrderFoodEntity orderFoodEntity : orderFoodsById) {
            orderFoodEntity.setUsersByUserId(this);
        }
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        UsersEntity that = (UsersEntity) o;

        if (id != that.id) return false;
        if (firstname != null ? !firstname.equals(that.firstname) : that.firstname != null) return false;
        if (lastname != null ? !lastname.equals(that.lastname) : that.lastname != null) return false;
        if (cnp != null ? !cnp.equals(that.cnp) : that.cnp != null) return false;
        if (email != null ? !email.equals(that.email) : that.email != null) return false;
        if (password != null ? !password.equals(that.password) : that.password != null) return false;
        if (phone != null ? !phone.equals(that.phone) : that.phone != null) return false;
        if (iban != null ? !iban.equals(that.iban) : that.iban != null) return false;
        if (address != null ? !address.equals(that.address) : that.address != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = id;
        result = 31 * result + (firstname != null ? firstname.hashCode() : 0);
        result = 31 * result + (lastname != null ? lastname.hashCode() : 0);
        result = 31 * result + (cnp != null ? cnp.hashCode() : 0);
        result = 31 * result + (email != null ? email.hashCode() : 0);
        result = 31 * result + (password != null ? password.hashCode() : 0);
        result = 31 * result + (phone != null ? phone.hashCode() : 0);
        result = 31 * result + (iban != null ? iban.hashCode() : 0);
        result = 31 * result + (address != null ? address.hashCode() : 0);
        return result;
    }


    public Collection<OrderFoodEntity> getOrderFoodsById() {
        return orderFoodsById;
    }

    public void setOrderFoodsById(Collection<OrderFoodEntity> orderFoodsById) {
        this.orderFoodsById = orderFoodsById;
    }

    @Override
    public String toString() {
        return "UsersEntity{" +
                "id=" + id +
                ", firstname='" + firstname + '\'' +
                ", lastname='" + lastname + '\'' +
                ", cnp='" + cnp + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                ", phone='" + phone + '\'' +
                ", iban='" + iban + '\'' +
                ", address='" + address + '\'' +
                ", order= " + orderFoodsById +
                '}';
    }
}

还有:

import javax.persistence.*;
import java.sql.Timestamp;
import java.util.Collection;

@Entity
@Table(name = "order_food", schema = "", catalog = "restaurant_java")
public class OrderFoodEntity {
    private int id;
    private int userId;
    private Timestamp created;
    private Timestamp closed;
    private Float total;
    @ManyToOne(cascade = {CascadeType.ALL})
    private UsersEntity usersByUserId;
    private Collection<OrderLineEntity> orderLinesById;

    @Id
    @Column(name = "id", nullable = false, insertable = true, updatable = true)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "user_id", nullable = false, insertable = true, updatable = true)
    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    @Basic
    @Column(name = "created", nullable = true, insertable = true, updatable = true)
    public Timestamp getCreated() {
        return created;
    }

    public void setCreated(Timestamp created) {
        this.created = created;
    }

    @Basic
    @Column(name = "closed", nullable = true, insertable = true, updatable = true)
    public Timestamp getClosed() {
        return closed;
    }

    public void setClosed(Timestamp closed) {
        this.closed = closed;
    }

    @Basic
    @Column(name = "total", nullable = true, insertable = true, updatable = true, precision = 0)
    public Float getTotal() {
        return total;
    }

    public void setTotal(Float total) {
        this.total = total;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        OrderFoodEntity that = (OrderFoodEntity) o;

        if (id != that.id) return false;
        if (userId != that.userId) return false;
        if (created != null ? !created.equals(that.created) : that.created != null) return false;
        if (closed != null ? !closed.equals(that.closed) : that.closed != null) return false;
        if (total != null ? !total.equals(that.total) : that.total != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = id;
        result = 31 * result + userId;
        result = 31 * result + (created != null ? created.hashCode() : 0);
        result = 31 * result + (closed != null ? closed.hashCode() : 0);
        result = 31 * result + (total != null ? total.hashCode() : 0);
        return result;
    }


    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false, insertable = true, updatable = true)
    public UsersEntity getUsersByUserId() {
        return usersByUserId;
    }

    public void setUsersByUserId(UsersEntity usersByUserId) {
        this.usersByUserId = usersByUserId;
    }

    @OneToMany(mappedBy = "orderFoodByOrderId")
    public Collection<OrderLineEntity> getOrderLinesById() {
        return orderLinesById;
    }

    public void setOrderLinesById(Collection<OrderLineEntity> orderLinesById) {
        this.orderLinesById = orderLinesById;
    }

    @Override
    public String toString() {
        return "OrderFoodEntity{" +
                "id=" + id +
                ", userId=" + userId +
                ", created=" + created +
                ", closed=" + closed +
                ", total=" + total +
                '}';
    }
}

谢谢。

Answer 1

表 order_line 中没有列 user_id。您拥有的是 order_id ，它没有意义，因为您在此表中已经有一个 id 。

确保这是正确的，并确保在您的 order_id (或 user_id 我认为这应该是您的情况)中插入现有的 id 来自您的用户表。