Why is there an error in my SQL foreign keys?

Question

CREATE TABLE BagelOrderLineItem (
  BagelOrderID INT NOT NULL,
  BagelID CHAR(2),
  BagelQuantity INT,
  PRIMARY KEY (BagelOrderID, BagelID),
  FOREIGN KEY (BagelOrderID) REFERENCES BagelOrder(BagelOrderID));
  
CREATE TABLE Bagel (
  BagelID CHAR(2) NOT NULL,
  BagelName VARCHAR(60),
  BagelDesc VARCHAR(30),
  BagelPrice DECIMAL(4,2),
  PRIMARY KEY (BagelID));
  
CREATE TABLE BagelOrder (
  BagelOrderID INT 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 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));

I'm trying to connect the primary and foreign keys to each other, but I'm not sure what's wrong and SQL Fiddle just says "Cannot add foreign key constraint".

Answer 1

I think the problem is the order in which you create the tables. You're referencing BagelOrder when you create BagelOrderLineItem, but at that point BagelOrder doesn't exist yet, so the reference fails. Try creating your Customer and Bagel first, then the BagelOrder and then BagelOrderLineItem.