在我的模拟数据库中出现多个外键约束错误，引用了错误的数据?

Question

我从这个SQL代码中得到了一些错误。我正在制作一个用于练习的模拟数据库。 我已经查看了一些解决方法，并重做了几次代码。

一切都是按顺序创建的(我相信(。任何帮助都会很棒！我是否引用了不正确的表/列？ 我是否引用了不应该引用的不同数据类型？

以下是我收到的错误：

Msg 547，级别 16，状态 0，第 186 行
插入语句与外键约束"fk_bookid"冲突。冲突发生在数据库"db_City_Library"中，表"dbo。书籍"，"书籍ID"一栏。

Msg 547，级别 16，状态 0，第 63 行
ALTER TABLE 语句与外键约束"fk_publisher_name_books"冲突。冲突发生在数据库"db_City_Library"中，表"dbo。出版商"，

法典：

CREATE DATABASE db_City_Library
USE db_City_Library
CREATE TABLE Library_Branch 
(
BranchID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),
BranchName varchar(100) NOT NULL,
BranchAddress varchar(100)
);
CREATE TABLE Book_Copies 
(
BookID INT NOT NULL,--FK Done
BranchID INT NOT NULL, --FK DONE
Number_Of_Copies INT NOT NULL
);
CREATE TABLE Books 
(
BookID INT NOT NULL PRIMARY KEY IDENTITY (10000, 1),
Title VARCHAR(100) NOT NULL,
PublisherName VARCHAR(100) --FK Done
);
CREATE TABLE Book_Authors 
(
BookID INT NOT NULL CONSTRAINT fk_book_id REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
AuthorName VARCHAR(100) NOT NULL
);
CREATE TABLE Publisher 
(
PublisherName VARCHAR(100) NOT NULL PRIMARY KEY,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR(15)
);
-- all code above executed
-- Customer information tables
CREATE TABLE Borrower 
(
CardNo INT NOT NULL PRIMARY KEY IDENTITY (100, 1),
Name VARCHAR(100) NOT NULL,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR (15) NOT NULL
);
CREATE TABLE Book_Loans 
(
BookID INT NOT NULL CONSTRAINT fk_bookid REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
BranchID INT NOT NULL CONSTRAINT fk_branch_id REFERENCES Library_Branch(BranchID) ON DELETE CASCADE ON UPDATE CASCADE,
CardNo INT NOt NULL CONSTRAINT fk_card_no REFERENCES Borrower(CardNo) ON DELETE CASCADE ON UPDATE CASCADE,
DateOut DATE NOT NULL,
DateIn DATE NOT NULL
);
ALTER TABLE Book_Copies
ADD CONSTRAINT fk_branch_id_book_copies 
FOREIGN KEY (BranchID) REFERENCES Library_Branch(BranchID) 
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Book_Copies
ADD CONSTRAINT fk_book_ID_book_copies 
FOREIGN KEY (BookID) REFERENCES Books(BookID) 
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Books
ADD CONSTRAINT fk_publisher_name_books
FOREIGN KEY (PublisherName) REFERENCES Publisher(PublisherName) 
ON DELETE CASCADE ON UPDATE CASCADE;
INSERT INTO Library_Branch (BranchName, BranchAddress)
VALUES ('Sharpstown', '121 Sharpstown Blvd'),
('Randolph', '17 Tamari Court'),
('Waterbury', '46 Henry Hough Rd'),
('Stowe', '192 Thomas Lane'),
('Burlington', '142 South Winooski Ave'),
('South Burlington', '190 South Willard Ave');
SELECT * FROM Publisher
INSERT INTO Borrower (Name, Address, Phone)
VALUES ('John Doe', '12 Thatplace place', '973-598-5837'),
('Justin Doughnut', '91 Thisplace place', '973-598-5832'),
('Jennifer Dee', '64 Programmer ave', '973-598-5981'),
('Katy Perry', '12 Thisandthatplace rd', '973-598-5837'),
('Louie Labrador', '75 State Route 109', '971-528-5997'),
('Susana Lander', '36 whoknows place', '575-555-9090'),
('Chee Mann', '90 Whoknows plaza', '802-253-9090'),
('Johnny Dee', '12 1st ave', '878-889-1010');
INSERT INTO Publisher (PublisherName, Address, Phone)
VALUES ('Pottermore Publishing', 'London, UK', '1-800-888-9087'),
('Bantam Spectra', 'New York, NY', '212-518-9090'),
('Scribner', 'New York, NY', '212-345-9090'),
('Allen & Unwin', 'Crows Nest, AU', '1-800-909-9813'),
('Penguin Random House', 'New York, NY', '1-800-733-3000'),
('Hatchette Livre', 'Paris France', '1-800-759-0190'),
('HarperCollins', 'New York, NY', '1-800-242-7737'),
('MacMillan Publishers', 'New York, NY', '1-800-901-9876'),
('Simon & Schuster', 'New York, NY', '1-866-506-1949'),
('McGraw-Hill Education', 'New York, NY', '1-800-338-3987');
SELECT * FROM Publisher;
SELECT* FROM Books
INSERT INTO Books (Title, PublisherName)
VALUES ('Harry Potter and the Halfblood Prince', 'Pottermore Publishing'),
('Harry Potter and the Prisoner of Azkaban', 'Pottermore Publishing'),
('Fire & Blood', 'Bantam Spectra'),
('A song of Ice and Fire', 'Bantam Spectra'),
('It', 'Scribner'),
('The Outsider', 'Scribner'),
('Different Seasons', 'Scribner'),
('The Hobbit', 'Allen & Unwin'),
('The Lord of the Rings: The Fellowship of the Ring', 'Allen & Unwin'),
('The Lord of the Rings: The Twin Towers', 'Allen & Unwin'),
('The Lord of the Rings: The Return of the King', 'Allen & Unwin'),
('The Guardians', 'Penguin Random House'),
('Talking to Strangers', 'Hachette Livre'),
('On the Come Up', 'HarperCollins'),
('Me', 'MacMillan Publishers'),
('The Institute', 'Simon & Schuster'),
('SAT/ACT Prep Book', 'McGraw-Hill Education'),
('Sleeping Beauties', 'Scribner'),
('Harry Potter and the Sorcerers Stone', 'Pottermore Publishing'),
('Howerd Stern Comes Again', 'Simon & Schuster');
INSERT INTO Books (Title, PublisherName)
VALUES ('The Lost Tribe', 'Hatchette Livre');
SELECT * FROM Book_Authors
SELECT * FROM Books
INSERT INTO Book_Authors (BookID, AuthorName)
VALUES (10000, 'J.K. Rawling'),
(10001, 'J.K. Rawling'),
(10002, 'George R.R. Martin'),
(10003, 'George R.R. Martin'),
(10004, 'Stephen King'),
(10005, 'Stephen King'),
(10007, 'J. R. R. Tolkein'),
(10008, 'J. R. R. Tolkein'),
(10009, 'J. R. R. Tolkein'),
(10010, 'J. R. R. Tolkein');
SELECT * FROM Book_Copies
SELECT* FROM Books
SELECT * FROM Library_Branch
INSERT INTO Book_Copies (BookID, BranchID, Number_Of_Copies)
VALUES (10002, 1, 4), (10004, 2, 2), (10001, 1, 6),
(10005, 3, 3), (10001, 2, 3), (10007, 2, 2), 
(10007, 3, 2), (10007, 4, 2), (10007, 5, 2),
(10008, 3, 2), (10009, 4, 2), (10010, 5, 2),
(10010, 6, 2), (10007, 1, 2);
SELECT * FROM Books
SELECT * FROM Library_Branch
SELECT * FROM Borrower
INSERT INTO Book_Loans (BookID, BranchID, CardNo, DateOut, DateIn)
VALUES (1000, 1, 101, '2020-04-21', '2020-05-21'),
(1002, 2, 100, '2020-03-12', '2020-04-12'),
(1000, 3, 102, '2020-04-01', '2020-05-01'),
(1000, 1, 101, '2020-05-02', '2020-06-02'),
(1000, 1, 104, '2020-05-20', '2020-06-20'),
(1000, 5, 107, '2020-05-20', '2020-06-20'),
(1000, 6, 105, '2020-04-17', '2020-05-17'),
(1000, 3, 102, '2020-06-01', '2020-07-01'),
(1000, 4, 102, '2020-01-01', '2020-02-01'),
(1000, 3, 107, '2020-05-22', '2020-06-22');

Answer 1

SQL真的很容易学习！(我看到你的语法是 = sql，如果我错了，请纠正我，哈哈(

我看到你得到这些错误，因为你的约束是你做的。
若要解决此问题，可以将ALTER TABLE tableName NOCHECK CONSTRAINT constraintName用于特定约束或ALTER TABLE tableName NOCHECK CONSTRAINT ALL用于所有约束。

您也可以通过ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT constraintName特定约束或ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL所有约束来启用它们。

我确实在 SQLFIDDLE 中为您重写了每个部分的上述所有内容......我只是使用上面的方法为您解决了第一个问题......我没有碰其他人，因为你想学习。如果您遇到困难，请告诉我，以便我可以帮助您解决卡住的地方。
DBFiddle Link 这将是简单的解决方案，而不是长期的解决方案
，如果您想要长期解决方案，请等待我在下面的编辑：

---

更新 #1第一个修复的问题

-- Update: #1 You are making PublisherName PK = UNIQUE so it can't be listed more than one time
CREATE TABLE Publisher (
PublisherName VARCHAR(100) NOT NULL, --PRIMARY KEY,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR(15)
);

所以你在这里需要做的是;

1. 选择另一个 PK/从发布者名称中删除该 PK
2. 不要使用相同的发布者，这显然不是最佳选择......
   
   具有更新 1 的 DBFiddle

---

更新 2
因此，fk_publisher_name_books约束将不再起作用，因为publisher_name不再是 PK/唯一。所以我们需要找出一个独特的...
我们可以在地址和电话之间进行选择。我确实选择了手机，因为每部手机=一个唯一的发布者。我确实将约束更改为fk_phone_books并更改了FK和引用。
如果我们也不改变表簿的结构，那将是没有完成的;

CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY IDENTITY(10000,1),
Title VARCHAR(100) NOT NULL,
PublisherName VARCHAR(100), --FK not good :(
Phone VARCHAR (15) NOT NULL --FK done right :P
);

带有 Update2 的新 DBFiddle

---

结论我想
花更多的时间来解决问题，但从我所看到的情况来看，您需要重写数据库的体系结构。
制作一个实体关系图，看看什么是PK，什么是FK。
首先制作 ERD 比写入/创建数据库/表要好得多，然后得到大量错误并搜索错误的 PK 和 FK ^^

Answer 2

您的脚本很容易出错。如果您自己生成脚本，我建议您始终遵循以下脚本运行的顺序。

1. 创建表 DDL 脚本。在创建表脚本中内联检查约束
2. 主键约束 DDL 脚本。更改表...添加约束...主键...
3. 外键约束 DDL 脚本。更改表...添加约束...外键。。。引用。。。
4. 为父表插入 DML 脚本。子表引用的数据。
5. 插入子表的 DML 脚本。依赖于父表数据的数据。

使用上述方法，可以在创建 PK、FK 错误 (DDL( 时避免 PK、FK 错误，并避免子表 (DML( 中的 FK 错误