When trying to run the following queries
INSERT INTO orders(customer_id, order_date, status)
VALUES (1, “2020-01-02”, 3);
INSERT INTO order_items
(LAST_INSERT_ID(), 1, 1, 2.95),
(LAST_INSERT_ID(), 1, 1, 3.95);
I get an error on the execution of the second query.
Error Code: 1062. Duplicate entry ‘11-1’ for key ‘order_items.PRIMARY’ 0.000 sec
Is it possible that you are using the same
LAST_INSERT_ID as the primary key for the both of the rows that you are inserting into
order_items? Each row needs to have a unique primary key.
You may want to specify the column names in your second
INSERT INTO statement, like you did with the first one, just to make sure that your values are lining up with the correct columns.
Yes, I got this problem also and its a typo! Your second set of values should be (LAST_INSERT_ID(),2,1,3.95).
Table order_items has a primary key based on (order_id, product_id), so you cant add that second record as both the order_id (from LAST_INSERT_ID()) and the product_id (1) would be the same as the first record inserted. That is what is causing the error.
(In case you are wondering why you cant have muliple products for the same order, you can, but you have to increase the quantity, you cant add duplicate primary keys.)
Hope that helps!