The concept and practical application of foreign keys in MySQL

The concept and practical application of foreign keys in MySQL

1. The concept of foreign keys

In database design, foreign keys Key is an important constraint used to describe the relationship between tables. Foreign keys are used to ensure that values in certain columns in one table must have corresponding values in corresponding columns in another table. The existence of foreign keys can ensure the consistency and integrity of data and avoid data insertion or updates that do not conform to logical relationships.

2. Practical application of foreign keys

In actual database design, foreign keys are widely used. The following uses specific code examples to illustrate the use of foreign keys in MySQL.

1. Create two related tables

First, we create two related tables, one is the orders table (orders) and the other is the customers table (customers ). The orders table will contain a foreign key to the customers table to establish the relationship between orders and customers.

CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(50) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

In the above code, we created two tables, the customers table and the orders table. The customer_id column in the orders table defines a foreign key pointing to the customer_id column in the customers table.

2. Insert data

Next, we insert some data into the customers table and orders table. First insert the data in the customers table:

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Alice'); INSERT INTO customers (customer_id, customer_name) VALUES (2, 'Bob');

Then insert the data in the orders table. Make sure that the inserted customer_id exists in the customers table:

INSERT INTO orders (order_id, order_date, customer_id) VALUES (1, '2022-01-01', 1); INSERT INTO orders (order_id, order_date, customer_id) VALUES (2, '2022-01-02', 2);

3. Test foreign key constraints

Next we demonstrate the constraint effect of foreign keys. When trying to insert an invalid customer_id, it will be restricted by foreign key constraints:

INSERT INTO orders (order_id, order_date, customer_id) VALUES (3, '2022-01-05', 3);

At this time, an error will be prompted because No record with customer_id 3 exists in the customers table, thus avoiding inserting inappropriate order data.

4. Foreign key operation rules

In MySQL, there are several operation rules for foreign key constraints, generally including CASCADE, SET NULL, RESTRICT, etc. Taking CASCADE as an example, when a customer is deleted from the customers table, the corresponding order data will be automatically deleted, avoiding data isolation.

CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );

Through the above examples, we can see the importance and practical application of foreign keys. Reasonable use of foreign key constraints in database design can ensure the consistency and integrity of data and avoid errors during data insertion and update. The concept of foreign keys in MySQL is not limited to the above examples. There are more complex scenarios in actual applications, which need to be used flexibly according to specific circumstances.

The above is the detailed content of The concept and practical application of foreign keys in MySQL. For more information, please follow other related articles on the PHP Chinese website!