无法添加外键约束错误时，当我尝试向表中添加第三个外键时发生错误

Question

我一直在尝试将属性O_BagelID添加到我的Order表中，但是每当我尝试添加它时，我都会收到错误"无法添加外键约束"。

如您所见，我已经确保 Order 中的外键和 Bagel 中的引用属于同一类型。当我删除O_BagelID和所有相关字段时，我不再收到该错误。

CREATE TABLE `Order`(
    OrderID INTEGER NOT NULL, 
    O_CustomerID INTEGER NOT NULL,  
    O_BagelCardID VARCHAR(16),
    O_BagelID INTEGER,  
    Order_date DATE NOT NULL, 
    Order_Cost DECIMAL(4,2) NOT NULL,
    Discount_applied BOOLEAN NOT NULL, 
    PRIMARY KEY(OrderID,O_CustomerID,O_BagelCardID,O_BagelID),
    FOREIGN KEY (O_CustomerID) 
    REFERENCES Customer(CustomerID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
    FOREIGN KEY (O_BagelCardID) 
    REFERENCES BagelCard(BagelCardID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
    FOREIGN KEY (O_BagelID) 
    REFERENCES Bagel(BagelID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE  
);
CREATE TABLE Bagel(
    BagelID INTEGER NOT NULL,
    Contains_Nuts BOOLEAN NOT NULL,
    Price DECIMAL(4,2) NOT NULL, 
    Description VARCHAR(255),
    PRIMARY KEY(BagelID)
);

Answer 1

您应该先创建表Bagel，然后创建表Order。当您创建对另一个表的引用时，它应该已经存在：

CREATE TABLE Bagel(
    BagelID INTEGER NOT NULL,
    Contains_Nuts BOOLEAN NOT NULL,
    Price DECIMAL(4,2) NOT NULL, 
    Description VARCHAR(255),
    PRIMARY KEY(BagelID)
);
CREATE TABLE `Order`(
    OrderID INTEGER NOT NULL, 
    O_CustomerID INTEGER NOT NULL,  
    O_BagelCardID VARCHAR(16),
    O_BagelID INTEGER,  
    Order_date DATE NOT NULL, 
    Order_Cost DECIMAL(4,2) NOT NULL,
    Discount_applied BOOLEAN NOT NULL, 
    PRIMARY KEY(OrderID,O_CustomerID,O_BagelCardID,O_BagelID),
    FOREIGN KEY (O_CustomerID) 
    REFERENCES Customer(CustomerID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
    FOREIGN KEY (O_BagelCardID) 
    REFERENCES BagelCard(BagelCardID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
    FOREIGN KEY (O_BagelID) 
    REFERENCES Bagel(BagelID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE  
);