0

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.

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;
2
Contributors
1
Reply
4
Views
5 Years
Discussion Span
Last Post by smantscheff
0

Make sure that the order_id and prod_id which you try to insert are already in the orders and products table. If they are, show your test data to debug this.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.