sql - 数据库在填充数据时关闭

问题描述

我有关于创建数据库的查询：

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, CONSTRAINT FK_m2m_books_authors_authorsFOREIGN KEY ( pfKa_id) REFERENCES authors( a_id) ON DELETE CASCADE ON UPDATE CASCADE)

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

标签： sql

sql - 数据库在填充数据时关闭

问题描述

解决方案

推荐阅读