What's wrong with my foreign key in my SQL code?

Question

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

The error message says "Cannot add or update a child row: a foreign key constraint fails (db_9_dc8b6f4.bagelorder, CONSTRAINT bagelorder_ibfk_1 FOREIGN KEY (CustID) REFERENCES customer (CustID))"

So I believe something is wrong with the foreign key on my Customer table, but I can't figure out what it is.

Answer 1

Based on this and the other question you asked recently, you really need to get this concept:

Code runs lines sequentially, from top to bottom, in most programming languages.

This means if a lower line of code hasn't run yet, then its effects haven't happened. If higher lines of code depend on those effects, then they will fail.

In this case, you are inserting a row to the BagelOrder table that depends on a few rows in the Customer table. If you try to reference those rows before they exist, the foreign key constraint is not satisfied.

Foreign keys require that the data exist in the referenced table (Customer in this case) before you can insert other rows that depend on them (in BagelOrder).

As an analogy: if you try to put on your shoes, you must put on your socks before the shoes.

Answer 2

You cannot have any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table (see mySQL docs).

So before you add the BagelOrder which references CustID

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

You need to have a matching Customer that has that custID so you need to change the order here to add the customer first.

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