mysql - SQL 错误代码 : 1452 when inserting data into table with FK referring to another table

Question

我目前正在尝试通过 SQL 脚本用数据填充我的表，但在填充我的 book 表时让我的 FK 合作时遇到了问题。我已经阅读了有关该问题的其他线程并尝试了 ON DELETE CASCADE 并且首先填充 FK 的其他建议对我也没有用。我还看到您可以覆盖 FK 检查，但这似乎不利于创建工作数据库。

我已经附加了我的脚本，create.sql 运行正常，但是 insert.sql 正如我所说的那样卡在了最后一个命令上。

感谢您的宝贵时间!

-----
create.sql
-----
CREATE TABLE Dept (
    Dept_ID INT(2) NOT NULL,
    Dept_Name CHAR(16) NOT NULL,
    Dept_Floor INT(1) NOT NULL,
    PRIMARY KEY (Dept_ID)
);

CREATE TABLE Publisher (
    Publisher_Name VARCHAR(50) NOT NULL,
    PubAddress VARCHAR(20) NOT NULL,
    Phone_Number VARCHAR(15) NOT NULL,
    PRIMARY KEY (Publisher_Name)
);

CREATE TABLE Book (
    Book_ID INT(6) NOT NULL,
    Title VARCHAR(32) NOT NULL,
    Dept_ID INT(2),
    Publisher_Name VARCHAR(20),
    PRIMARY KEY (Book_ID),
    FOREIGN KEY (Dept_ID) REFERENCES Dept(Dept_ID),
    FOREIGN KEY (Publisher_Name) REFERENCES Publisher(Publisher_Name)
);
-----
insert.sql
-----
INSERT INTO Dept (Dept_ID, Dept_Name, Dept_Floor) 
VALUES ('01', 'Fantasy', '1'), ('02', 'Sci-Fi', '1'), ('03', 'Fiction', '2'), ('04', 'Educational', '1'), ('05', 'Crime', '2');

INSERT INTO Publisher (Publisher_Name, PubAddress, Phone_Number)
VALUES ('Bloomsbury', '234 Hay Street', '14325673489'), ('Charles Scribner','236 Hone Way','173677654765'),('Pigeon Books','5444 North Street','17884787644'), ('Academic Press', '2354 Bridge Road', '175634598234'), ('Bantam Books', '2623 River Road', '17234648444');

INSERT INTO Book (Book_ID, Title, Dept_ID, Publisher_Name) 
VALUES ('000001','Harry Potter and the Goblet of Fire', '01', 'Bloomsbury'), ('000002','A Brief History of Time', '04', 'Puffin Books'), ('000003','The Great Gatsby', 'Charles Scribners Sons', '03'), ('000004','Advances in Ecological Research', 'Academic Press', '04'), ('000005','The Case of Joe', 'Bantam Books', '05');

Answer 1

您的列没有对齐，您的数据长度对于给定的数据来说太小了。

CREATE TABLE Dept (
    Dept_ID INT(2) NOT NULL,
    Dept_Name CHAR(16) NOT NULL,
    Dept_Floor INT(1) NOT NULL,
    PRIMARY KEY (Dept_ID)
);

CREATE TABLE Publisher (
    Publisher_Name VARCHAR(50) NOT NULL,
    PubAddress VARCHAR(20) NOT NULL,
    Phone_Number VARCHAR(15) NOT NULL,
    PRIMARY KEY (Publisher_Name)
);

CREATE TABLE Book (
    Book_ID INT(6) NOT NULL,
    Title VARCHAR(40) NOT NULL,
    Dept_ID INT(2),
    Publisher_Name VARCHAR(50),
    PRIMARY KEY (Book_ID),
    FOREIGN KEY (Dept_ID) REFERENCES Dept(Dept_ID),
    FOREIGN KEY (Publisher_Name) REFERENCES Publisher(Publisher_Name)
);

INSERT INTO Dept (Dept_ID, Dept_Name, Dept_Floor) 
VALUES ('01', 'Fantasy', '1'), ('02', 'Sci-Fi', '1'), ('03', 'Fiction', '2'), ('04', 'Educational', '1'), ('05', 'Crime', '2');

INSERT INTO Publisher (Publisher_Name, PubAddress, Phone_Number)
VALUES ('Bloomsbury', '234 Hay Street', '14325673489'), ('Charles Scribner','236 Hone Way','173677654765'),('Pigeon Books','5444 North Street','17884787644'), ('Academic Press', '2354 Bridge Road', '175634598234'), ('Bantam Books', '2623 River Road', '17234648444');

INSERT INTO Book (Book_ID, Title, Dept_ID, Publisher_Name) 
VALUES ('000001','Harry Potter and the Goblet of Fire', '01', 'Bloomsbury'), ('000002','A Brief History of Time', '04', 'Bantam Books'), ('000003','The Great Gatsby', '03', 'Charles Scribner'), ('000004','Advances in Ecological Research', '04', 'Academic Press'), ('000005','The Case of Joe',  '05', 'Bantam Books');