Cannot add foreign key constraint error occurs when I try to add a THIRD Foreign Key to my table

Question

I have been trying to add the attribute O_BagelID into my Order Table, however whenever I attempt to add it in, I get the error "Cannot add foreign key constraint".

As you can see I've made sure that both the foreign key in Order, and the reference in Bagel are of the same type. When I remove O_BagelID and all the associated fields, I no longer get that error.

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

You should create table Bagel first and then create table Order. When you create REFERENCES to another table it should be already exist:

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