MySQL Forums
Forum List  »  Newbie

Re: How to get rid of Composite Primary Key
Posted by: Phillip Ward
Date: April 28, 2021 06:13AM

Quote

My Order Table format is:
Order_ID, Item_ID, Item_Qty,Price, Total.......

That should be two tables, not one.
An "Order" table that summaries the whole thing, and an "Order Item" that holds the detail of each item within that Order.

create table Orders 
( id int not null auto_increment 
, total decimal( 9, 2 ) 
, primary key ( id )
); 

create table Order_Items 
( order_id int not null 
, item_id int not null 
, qty int not null 
, price decimal(9.2) not null 
, primary key ( order_id, item_id )
, foreign key order_id references Orders( id ) 
, foreign key item_id references Items( id ) /* Assumes an Items table that defines anything that can be ordered */ 
); 

select * 
from Orders ; 

+----+-------+
| id | total |
+----+-------+
|  1 |  2.98 |
+----+-------+

select * 
from Order_Items ; 

+----------+---------+-----+-------+
| order_id | item_id | qty | price |
+----------+---------+-----+-------+
|        1 |      22 |   1 |  1.99 |
|        1 |      33 |   1 |  0.99 |
+----------+---------+-----+-------+

Regards, Phill W.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.