DB在填充数据时出现故障

Question

我有数据库创建的查询：

CREATE DATABASE IF NOT EXISTS Library;
USE Library;
CREATE TABLE IF NOT EXISTS Subscribers
(
s_id   INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
s_name VARCHAR(150)                                NOT NULL
);
CREATE TABLE IF NOT EXISTS Subscriptions
(
sb_id         INTEGER UNSIGNED PRIMARY KEY NOT NULL,
sb_subscriber INTEGER UNSIGNED             NOT NULL,
sb_book       INTEGER UNSIGNED             NOT NULL,
sb_start      DATE                         NOT NULL,
sb_finish     DATE                         NOT NULL,
sb_is_active  ENUM ('Y', 'N')              NOT NULL
);
CREATE TABLE IF NOT EXISTS Books
(
b_id       INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
b_name     VARCHAR(150)                                NOT NULL,
b_year     SMALLINT UNSIGNED                           NOT NULL,
b_quantity SMALLINT UNSIGNED                           NOT NULL
);
CREATE TABLE IF NOT EXISTS Genres
(
g_id   INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
g_name VARCHAR(150) UNIQUE                         NOT NULL
);
CREATE TABLE IF NOT EXISTS Authors
(
a_id   INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
a_name VARCHAR(150)                                NOT NULL
);
CREATE TABLE IF NOT EXISTS m2m_books_genres
(
pfKb_id INTEGER UNSIGNED NOT NULL,
pfKg_id INTEGER UNSIGNED NOT NULL,
PRIMARY KEY PK_m2m_books_genres (pfKb_id, pfKg_id)
);
CREATE TABLE IF NOT EXISTS m2m_books_authors
(
pfKb_id INTEGER UNSIGNED NOT NULL,
pfKa_id INTEGER UNSIGNED NOT NULL,
PRIMARY KEY PK_m2m_books_authors (pfKb_id, pfKa_id)
);
ALTER TABLE Subscriptions
ADD CONSTRAINT FK_subscriptions_books
FOREIGN KEY (sb_book)
REFERENCES Books (b_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Subscriptions
ADD CONSTRAINT FK_subscriptions_subscribers
FOREIGN KEY (sb_subscriber)
REFERENCES Subscribers (s_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE m2m_books_genres
ADD CONSTRAINT FK_m2m_books_genres_books
FOREIGN KEY (pfKb_id)
REFERENCES Books (b_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE m2m_books_genres
ADD CONSTRAINT FK_m2m_books_genres_genres
FOREIGN KEY (pfKg_id)
REFERENCES Genres (g_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE m2m_books_authors
ADD CONSTRAINT FK_m2m_books_authors_authors
FOREIGN KEY (pfKa_id)
REFERENCES Authors (a_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE m2m_books_authors
ADD CONSTRAINT FK_m2m_books_authors_books
FOREIGN KEY (pfKb_id)
REFERENCES Books (b_id)
ON DELETE CASCADE ON UPDATE CASCADE;

以及用数据填充数据库的查询：

USE library;
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Евгений Онегин', 1985, 2);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Сказка о рыбаке и рыбке', 1990, 3);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Основания и империя', 2000, 5);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Психология и программирование', 1998, 1);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Язык программирования С++', 1996, 3);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Курс теоритической физики', 1981, 12);
INSERT INTO books (b_name, b_year, b_quantity)
VALUES ('Искусство программирования', 1993, 7);
INSERT INTO authors (a_name)
VALUES ('Д. Кнут');
INSERT INTO authors (a_name)
VALUES ('А. Азимов');
INSERT INTO authors (a_name)
VALUES ('Л.Д. Ландау');
INSERT INTO authors (a_name)
VALUES ('Е.М. Лифшиц');
INSERT INTO authors (a_name)
VALUES ('Б. Страуструп');
INSERT INTO authors (a_name)
VALUES ('А.С. Пушкин');
INSERT INTO genres (g_name)
VALUES ('Поэзия');
INSERT INTO genres (g_name)
VALUES ('Программирование');
INSERT INTO genres (g_name)
VALUES ('Психология');
INSERT INTO genres (g_name)
VALUES ('Наука');
INSERT INTO genres (g_name)
VALUES ('Классика');
INSERT INTO genres (g_name)
VALUES ('Фантастика');
INSERT INTO subscribers (s_name)
VALUES ('Иванов И.И.');
INSERT INTO subscribers (s_name)
VALUES ('Петров П.П.');
INSERT INTO subscribers (s_name)
VALUES ('Сидоров С.С.');
INSERT INTO subscribers (s_name)
VALUES ('Сидоров С.С.');
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (1, 7);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (2, 7);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (3, 2);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (4, 3);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (4, 6);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (5, 6);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (6, 5);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (6, 4);
INSERT INTO m2m_books_authors (pfKb_id, pfKa_id)
VALUES (7, 1);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (1, 1);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (1, 5);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (2, 1);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (2, 5);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (3, 6);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (4, 2);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (4, 3);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (5, 2);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (6, 5);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (7, 2);
INSERT INTO m2m_books_genres (pfKb_id, pfKg_id)
VALUES (7, 5);
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (100, 1, 3, 2011 - 01 - 12, 2011 - 02 - 12, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (2, 1, 1, 2011 - 01 - 12, 2011 - 02 - 12, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (3, 3, 3, 2012 - 05 - 17, 2012 - 07 - 12, 'Y');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (42, 1, 2, 2012 - 06 - 11, 2012 - 08 - 11, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (57, 4, 5, 2012 - 06 - 11, 2012 - 08 - 11, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (61, 1, 7, 2014 - 08 - 03, 2014 - 10 - 03, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (62, 3, 5, 2014 - 08 - 3, 2014 - 10 - 03, 'Y');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (86, 3, 1, 2014 - 08 - 03, 2014 - 09 - 03, 'Y');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (91, 4, 1, 2015 - 10 - 07, 2015 - 03 - 07, 'Y');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (95, 1, 4, 2015 - 10 - 07, 2015 - 11 - 07, 'N');
INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (99, 4, 4, 2015 - 10 - 08, 2025 - 11 - 08, 'Y');

长话短说，我得到这个：

[23000][1452]无法添加或更新子行：外键约束失败(library.m2m_books_authors，constraintFK_m2m_books_authors_authorsFOREING key(pfKa_id(REFERENCESauthors(a_id(ON DELETE CASCADE ON update CASCADE(

我知道解决方案SET FOREIGN_KEY_CHECKS=0，但我想正确解决这个问题，并找出为什么会发生这个错误。谢谢你的回答，我很感激。

Answer 1

如果您仔细查看，表authors中的列a_id可能是IDENTITY，您只插入了6个作者，并且您正试图为可能不存在的作者id 7分配一些内容。

Answer 2

如果有人会用我的代码学习或其他什么，还有一个错误：

使用此：

INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (100, 1, 3, '2011-01-12', '2011-02-12', 'N');

而不是这个：

INSERT INTO subscriptions (sb_id, sb_subscriber, sb_book, sb_start, sb_finish, sb_is_active)
VALUES (100, 1, 3, 2011 - 01 - 12, 2011 - 02 - 12, 'N');

当然，请看@Juanjo的回复。