Visualize Relationships and a Multicolumn Unique Index
Explore how to visualize and manage foreign key relationships in MySQL and understand the importance of creating a unique multi-column index to prevent duplicate product entries in the same order. This lesson guides you through inserting data, setting constraints, and verifying your unique index functionality to ensure consistent and reliable relational data.
We'll cover the following...
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:
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 ...