Cannot add or update a child row: a foreign key constraint fails (`hw2`.`order_item`,?

Question

I'm trying to do an insert into my order_item table, but I'm getting this error. Can someone help with why this is happening. Thank you.

My sql code to create the tables is below.

MySQL Syntax (Toggle Plain Text)

DROP TABLE IF EXISTS order_item, orders, customer, product;

CREATE TABLE customer
( id INT(10) NOT NULL AUTO_INCREMENT,
lastname VARCHAR(25),
firstname VARCHAR(15),
username VARCHAR(25),
password VARCHAR(25),
email VARCHAR(35),
address VARCHAR(35),
city VARCHAR(15),
state CHAR(2),
zip5 CHAR(5),
PRIMARY KEY (id)
) ENGINE = INNODB;


CREATE TABLE product
( id INT(12) NOT NULL AUTO_INCREMENT,
product_name VARCHAR(255),
details TEXT,
price VARCHAR(16),
date_added DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;


CREATE TABLE orders
( id INT(10) NOT NULL AUTO_INCREMENT,
customer INT(10),
order_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (customer) REFERENCES customer (id)
) ENGINE = INNODB;

CREATE TABLE order_item
( order_id INT(10),
prod_id INT(12),
qty SMALLINT DEFAULT 1,
FOREIGN KEY (order_id) REFERENCES orders (id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (prod_id) REFERENCES product (id),
PRIMARY KEY (order_id, prod_id)
) ENGINE = INNODB;

Answer 1

Best Answer - Chosen by Voters

Did you do the insert into table 'orders' first? Because of the foreign key constraint, orders.id must be populated before attempting to populate order_item.order_id