/* drops my_order_items table if exists (and if needed) */
/* DROP TABLE IF EXISTS my_order_items; */

/* create the my_orders table */
CREATE TABLE my_order_items(
    order_item_id INT NOT NULL AUTO_INCREMENT,
    order_item_name VARCHAR(50) NOT NULL,
    order_item_status VARCHAR(15) NOT NULL,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(6,2) NOT NULL,
    PRIMARY KEY(order_item_id),
    FOREIGN KEY(order_id) REFERENCES my_orders(order_id),
    FOREIGN KEY(product_id) REFERENCES my_products(product_id)
);