CREATE TABLE Bagel (
BagelID CHAR(2) NOT NULL,
BagelName VARCHAR(30),
BagelPrice DECIMAL(4,2),
BagelDesc VARCHAR(30),
PRIMARY KEY (BagelID));
CREATE TABLE Customer (
CustID INT NOT NULL,
FirstName VARCHAR(30),
LastName VARCHAR(60),
Address1 VARCHAR(60),
Address2 VARCHAR(60),
City VARCHAR(30),
State CHAR(2),
Zip INT,
MobilePhone VARCHAR(30),
PRIMARY KEY (CustID));
CREATE TABLE BagelOrder (
BagelOrderID SMALLINT NOT NULL,
CustID INT NOT NULL,
OrderDate DATE,
DeliveryFee DECIMAL(4,2),
SpecNotes VARCHAR(60),
PRIMARY KEY (BagelOrderID),
FOREIGN KEY (CustID) REFERENCES Customer(CustID));
CREATE TABLE BagelOrderLineItem (
BagelOrderID SMALLINT NOT NULL,
BagelID CHAR(2) NOT NULL,
BagelQuantity TINYINT,
PRIMARY KEY (BagelOrderID, BagelID),
FOREIGN KEY (BagelOrderID) REFERENCES BagelOrder(BagelOrderID)
);
INSERT INTO BagelOrder (BagelOrderID, CustID, OrderDate, DeliveryFee, SpecNotes)
VALUES
(1, 1, '2023-12-07', 5.99, 'Add Cream Cheese'),
(2, 2, '2023-12-07', 5.99, NULL),
(3, 3, '2023-12-14', 6.99, NULL);
INSERT INTO BagelOrderLineItem (BagelOrderID, BagelID, BagelQuantity)
VALUES
(1, 'RB', 5),
(2, 'EB', 2),
(3, 'CB', 1),
(3, 'NB', 2);
INSERT INTO Customer (CustID, FirstName, LastName, Address1, Address2, City, State, Zip, MobilePhone)
(1, 'Bryn', NULL, '123 ABC Street', NULL, 'Salt Lake City', 'UT', 84101, '8011234567'),
(2, 'Santa', 'Clause', '1234 S Pole St', NULL, 'Vernal', 'UT', 84078, '8018001234'),
(3, 'Jane', 'Doe', '1000 W Street', NULL, 'New York City', 'NY', 10000, '8001231234');
INSERT INTO Bagel (BagelID, BagelName, BagelPrice, BagelDesc)
VALUES
('RB', 'Radically Raisin Bagel', 2.00, 'Raisin Bagel'),
('EB', 'All the Stuff Bagel', 2.35, 'Everything Bagel'),
('CB', 'Chip Off the Block Bagel', 2.25, 'Chocolate Chip Bagel'),
('MB', "Keepin' It Healthy Bagel", 2.00, 'Multigrain Bagel'),
('NB', 'Classic New York Bagel', 1.75, 'New York Bagel');
错误消息显示"无法添加或更新子行:外键约束失败(db_9_dc8b6f4
.bagelorder
,约束bagelorder_ibfk_1
外键(CustID
)引用customer
(CustID
))"
所以我认为我的Customer表上的外键有问题,但我找不出它是什么.