Insert the Wrong Customer Id
Understand the importance of foreign key constraints in relational databases. Learn how to define and apply these constraints in MySQL to protect your tables from invalid data by linking orders to existing customers, ensuring data accuracy and integrity.
We'll cover the following...
Insert a nonexistent customer_id
Let’s see our customers and orders again.
mysql> select * from customers;
+----+-----------+----------+
| id | name | identity |
+----+-----------+----------+
| 1 | John Woo | JW0001 |
| 3 | Maria Moo | ML0001 |
+----+-----------+----------+
2 rows in set (0.00 sec)
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)
The order with an id of 1 refers to the customer with id of 1 via the customer_id column.
Let’s try to insert a new order that references a nonexistent customer. Try the following command:
Oops! The order has been inserted without any error or warning. Orders that refer to nonexistent customers aren’t good. They’re invalid data. This is where it becomes an issue that MySQL doesn’t know there’s a relationship between the orders and customers tables.
We need to make this relationship explicit so that MySQL will protect us from inserting such invalid data.
In order to make the relationship explicit, we need to create a database-level constraint, which is called the foreign key constraint.
Before we do that, let’s delete the bad order:
mysql> delete from orders where id = 4;
Query OK, 1 row