Insert the Wrong Customer Id

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. ...