mysql - 如何使工作 : FOREIGN KEY error during addition

Question

我有两个表，books 和borrowing requests - 我正在做一个学习项目来学习 MySQL。尝试使用 FK 链接这两个表，并不断出错:

 "SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")

除了文档之外，我搜索了几乎所有关于 SO 的相关主题，但是没有成功。类型相同，列都是无符号的，一个主键，等等——据我所知，我遵守了所有规定。但它不起作用!我错过了什么？

下面是两个表和fk加法查询:

CREATE TABLE `books` (
  `book_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `book_title` VARCHAR(100) NOT NULL,
  `author_id` INT(11) NOT NULL, 
  `book_condition` INT(5) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=23453 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `borrowing_requests` (
  `request_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT(11) NOT NULL,
  `book_id` INT(11) UNSIGNED NOT NULL,
  `due_date` VARCHAR(55) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=23453 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

FK 查询:

ALTER TABLE `books` ADD CONSTRAINT books_fk FOREIGN KEY (`book_id`) 
REFERENCES `borrowing_requests`(`book_id`) 
ON DELETE NO ACTION ON UPDATE NO ACTION

Answer 1

你有“向后”的外键——借阅请求应该引用一本书，而不是相反:

ALTER TABLE `borrowing_requests` ADD CONSTRAINT books_fk FOREIGN KEY (`book_id`) 
REFERENCES `books`(`book_id`) 
ON DELETE NO ACTION ON UPDATE NO ACTION