When two tables are connected using MySQL FOREIGN KEY, how can we say that the data in the child table maintains integrity?

In fact, foreign keys enforce referential integrity and help us automatically maintain the consistency and integrity of the data. It can be understood using the examples of the two tables "customer" and "orders". Here, "customer" is the parent table and "orders" is the child table. We cannot create orders for non-existent customers. This can be demonstrated by inserting values ​​in both tables as shown below -

mysql> Select * from Customer;
+----+--------+
| id | name   |
+----+--------+
| 1  | Gaurav |
| 2  | Raman  |
| 3  | Harshit|
| 4  | Aarav  |
+----+--------+
4 rows in set (0.00 sec)
mysql> Select * from orders;
+-----------+-------------+------+
| order_id  | product     | id   |
+-----------+-------------+------+
| 100       | Notebook    | 1    |
| 110       | Pen         | 1    |
| 120       | Book        | 2    |
| 130       | Charts      | 2    |
+-----------+-------------+------+
4 rows in set (0.00 sec)

Now, suppose we try to insert a value of a non-existent customer in the "orders" table (id 10 does not exist in "customer" ” table), MySQL will throw an error due to the following error: Foreign key constraint.

mysql> insert into orders values(400, 'Notebook',10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`query`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id`) REFERENCES `customer` (`id`))

The above is the detailed content of When two tables are connected using MySQL FOREIGN KEY, how can we say that the data in the child table maintains integrity?. For more information, please follow other related articles on the PHP Chinese website!