How to use foreign keys in MySQL to maintain data integrity?

How to use foreign keys in MySQL to maintain data integrity?

Introduction:
In a database, data integrity is very important. By using foreign keys, we ensure that data consistency and integrity are maintained between related tables in the database. This article will introduce you to how to use foreign keys to maintain data integrity in MySQL.

1. Create tables and associations:

First, we need to create two tables and associate them. Suppose we have two tables, one is the "orders" table and the other is the "customers" table. Each order needs to be associated with a customer, and we will use foreign keys to maintain this association.

The SQL statement to create the "customers" table is as follows:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

The SQL statement to create the "orders" table is as follows:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(10,2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In the above "orders" table, we added a column called "customer_id" and used the "id" column of the "customers" table in the foreign key constraint.

2. Insert data:

Next, we insert some data into the "customers" table and the "orders" table. Here is the SQL statement for some sample data:

-- 插入customers表格的数据 --
INSERT INTO customers (name, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO customers (name, email) VALUES ('李四', 'lisi@example.com');
INSERT INTO customers (name, email) VALUES ('王五', 'wangwu@example.com');

-- 插入orders表格的数据 --
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2021-01-01', 100.00, 1);
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2021-01-02', 200.00, 2);
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2021-01-03', 300.00, 3);

In the above SQL statement, we are inserting the order data by using the "id" value from the "customers" table associated with the foreign key.

3. Constraints and maintenance of foreign keys:

Now, let’s test the constraints and maintenance of foreign keys. We try to execute the following statement:

-- 尝试在orders表格中插入无效的customer_id值 --
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2021-01-04', 400.00, 4);

Due to the existence of foreign key constraints, this insert operation will fail and an error message will be displayed.

In MySQL, foreign keys can also be defined with the "ON DELETE" and "ON UPDATE" options to specify the action that should be taken when a row in the associated table is deleted or updated. Here is an example:

-- 创建orders表格时定义外键的ON DELETE和ON UPDATE选项 --
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(10,2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE
);

In the above example, we set the "ON DELETE CASCADE" and "ON UPDATE CASCADE" options of the foreign key, which means that if the row in the "customers" table is deleted or Updated, all rows in the "orders" table associated with it will also be deleted or updated accordingly.

Summary:

By using foreign keys, we can ensure the consistency and integrity of data between related tables in the database. In MySQL, we can use the "FOREIGN KEY" keyword to create foreign keys and be constrained when inserting or updating data. In addition, we can use the "ON DELETE" and "ON UPDATE" options to define the action that should be taken when the rows related by the foreign key are deleted or updated in the related table.

The above is an introduction on how to use foreign keys in MySQL to maintain data integrity. I hope it will be helpful to you. thanks for reading!

The above is the detailed content of How to use foreign keys in MySQL to maintain data integrity?. For more information, please follow other related articles on the PHP Chinese website!