无法添加或更新子行:外键约束在mysql hibernate中失败

mi7gmzs6  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(190)

这似乎是一个常见的错误。我看过其他的主题,但没什么变化。以下是mysql脚本:

DROP DATABASE IF EXISTS `library`;
CREATE DATABASE  IF NOT EXISTS `library`;
USE `library`;

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0; 

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE customer
(
  c_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name CHAR(50) NOT NULL,
  last_name CHAR(50) NOT NULL,
  email CHAR(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO customer VALUES 
(1,'David','Bush','bushd@gg.com'),
(2,'John','Doe','johnd@gg.com'),
(3,'Max','Rao','maxr@gg.com'),
(4,'Mary','James','maryj@gg.com'),
(5,'Tony','Lord','tony@gg.com');

CREATE TABLE rental
(
  rental_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  issue_date DATE,
  return_date DATE,
  customer_id INT,
  book_id INT,
  KEY `FK_CUSTOMER_idx` (`customer_id`),
  CONSTRAINT `FK_CUSTOMER` 
  FOREIGN KEY (`customer_id`) 
  REFERENCES `customer` (`c_id`),

  KEY `FK_BOOK_idx` (`book_id`),
  CONSTRAINT `FK_BOOK` 
  FOREIGN KEY (`book_id`) 
  REFERENCES `book` (`b_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO rental VALUES
(1,'2014-10-11',null,3,1),
(2,'2014-10-11',null,5,2);

CREATE TABLE book
(
  b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author  CHAR(50) NOT NULL,
  title CHAR(100) NOT NULL,
  category CHAR(50) NOT NULL,
  availability CHAR(5) NOT NULL DEFAULT 'yes'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO book VALUES
(1,'Doyle','The adventures of binkle and flip','Child','no'),
(2,'Blyton','The adventures of sherlock holmes','Crime fiction','no'),
(3,'Rustard','Insects','Science','yes'),
(4,'Larsson','Man who hate woman','Crime','yes'),
(5,'Tolkien','Lord of the Rings: The Two Towers','Fantasy','lost');

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES; 

SET FOREIGN_KEY_CHECKS=1;

错误如下:

ERROR: Cannot add or update a child row: a foreign key constraint fails 
(`library`.`rental`, CONSTRAINT `FK_BOOK` FOREIGN KEY (`book_id`) REFERENCES 
`book` (`b_id`))

租赁实体:

@Entity
@Table(name="rental")
public class Rental {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="rental_id")
private int id;
@Column(name="issue_date")
private Date issueDate;
@Column(name="return_date")
private Date returnDate;

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name="book_id", referencedColumnName="b_id")
private Book book;
@ManyToOne(cascade=CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name="customer_id", referencedColumnName="c_id")
private Customer customer;

客户实体:

@Entity
@Table(name="customer")
public class Customer {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="c_id")
private int id;
@NotNull
@Column(name="first_name")
private String firstName;
@NotNull
@Column(name="last_name")
private String lastName;
@Email
@Column(name="email")
private String email;

@OneToMany(mappedBy="customer")
private List<Rental> rentals;

账簿实体:

@Entity
@Table(name="book")
public class Book {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="b_id")
private int id;
@NotNull
@Column(name="author")
private String author;
@NotNull
@Column(name="title")
private String title;
@NotNull
@Column(name="category")
private String category;
@NotNull
@NotEmpty
@Column(name="availability")
private String availability;

@OneToMany(mappedBy="book")
private List<Rental> rentals;

控制器(测试用硬编码值):

@PostMapping("/confirmRent")
public String confirmRent(/*PathVariable ("customerId") int cId, 
@PathVariable ("bookId") int bId*/) {
    int cId = 5;
    int bId = 5;

    Customer theCustomer = customerService.getCustomer(cId);
    Book theBook = bookService.getBook(bId);
    rentalService.createNewEntry(theCustomer, theBook);
    return "redirect:/book/allAvailableBooks";
}

道:

@Override
public void createNewEntry(Customer theCustomer, Book theBook) {
    Session currentSession = sessionFactory.getCurrentSession();
    Rental rental = new Rental();
    rental.setBook(theBook);
    rental.setCustomer(theCustomer);
    currentSession.save(rental);

}

我错过什么了吗?附言:代码可能看起来很业余:我是初学者;)

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题