mysql - 当我尝试向表中添加第三个外键时，出现无法添加外键约束错误

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  
);