...

/

Visualize Relationships and a Multicolumn Unique Index

Visualize Relationships and a Multicolumn Unique Index

Learn to enter new data into order_items and create a unique index on multiple columns.

Visualize the relationships after insert

Let’s see the data that we have in our orders:

mysql> select * from orders;
+----+--------------+---------------------+-------------+
| id | order_number | ordered_at          | customer_id |
+----+--------------+---------------------+-------------+
|  1 | ABC001       | 2016-09-09 08:34:55 |           1 |
+----+--------------+---------------------+-------------+
1 row in set (0.00 sec)

Let’s also see our products:

mysql> select * from products;
+----+-----------------------------+-------+
| id | name                        | price |
+----+-----------------------------+-------+
|  1 | Game of Thrones - S01 - DVD | 50.00 |
|  2 | Of Mice and Men             | 19.80 |
|  3 | A Nice Story                |  5.00 |
+----+-----------------------------+-------+
3 rows in set (0.00 sec)

Now, let’s insert the details of the first order with an id of 1. We’ll sell two DVDs of the Game of Thrones - S01 - DVD product:

Press + to interact
insert into order_items (order_id, product_id, price, quantity) values (1, 1, 50.00, 2);

This command isn’t new to us. We only have to make sure that we give the correct reference values. So, for the order_id, we give the value 1 because we want the new details entry (the new entry in order_items) to reference the first order that has an id of 1. We also set the product_id to have the value 1 because ...