mysql foreign key settings

MySQL is one of the most popular open source relational database management systems. Although MySQL is very powerful, it sometimes becomes very complex and causes problems when faced with complex data table structures and relationships. To solve this problem, MySQL provides foreign key constraints. Foreign key constraints are a way to ensure the integrity of relationships between data tables. In this article, we will delve into MySQL's foreign key settings and related applications.

What is a foreign key?

In the database, the columns of a table are called fields or attributes of the table. For example, in the database of an e-commerce website, the user's mobile phone number may be stored in a table named "user" (user), and the user's order is stored in another table named "order" (order) middle. There is a relationship between these two tables. A user can place multiple orders, and an order can only belong to one user.

Foreign key is a relational database term that is a constraint that joins two tables. Foreign key constraints ensure that when one table references data in another table, its instance must match a value in the other table.

The advantage of foreign keys is that they ensure the integrity of the relationships between data tables. This means that if there is a foreign key constraint between two tables, when a record in the primary table is deleted or updated, all foreign keys that depend on it will be automatically deleted or updated.

How to set foreign keys?

In order to set up a foreign key, you need to make sure that the MySQL engine you are using supports it. Currently, MySQL supports MyISAM, InnoDB and NDB engines, of which InnoDB is the most widely used engine and the only MySQL engine that supports foreign keys.

If you want to use foreign key constraints, make sure your table is using the InnoDB engine. You can execute the following command to verify the engine type of the table:

SHOW TABLE STATUS FROM database_name WHERE Name='table_name';

where database_name is the database name, and table_name is the name of the table you want to check the engine type.

If your table is using an engine that does not support foreign key constraints, you need to change it to the InnoDB engine and execute the following command:

ALTER TABLE table_name ENGINE=InnoDB;

Next, you need to create a table and Add a foreign key constraint. The following is the corresponding code example:

CREATE TABLE order (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date_added DATETIME,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);

In this example, we create an "order" (order) table, which includes the order ID (order_id), user ID (user_id) and date (date_added) fields . We then added a foreign key constraint to the "Orders" table to ensure that the user ID exists before adding a new order.

In this constraint expression, user_id is the field name in the current table, user is the name of the dependent table, user_id Is the field name in the dependent table. In addition, the FOREIGN KEY keyword is used to define foreign key constraints.

In MySQL, foreign key constraints can also be defined as CASCADE, SET NULL, or RESTRICT. Here are descriptions of these options:

• CASCADE: When a parent is specified, the child will also be deleted or updated.
• SET NULL: The value of the child item will be deleted or updated to be Null when the parent item is specified.
• RESTRICT: Prevents deletion or updating of children when a parent is specified.

The following is a complete code example with CASCADE, SET NULL and RESTRICT selections:

-- 创建父表
CREATE TABLE user (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL
);

-- 创建子表，并定义外键约束
CREATE TABLE order (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date_added DATETIME,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
        ON DELETE CASCADE
        ON UPDATE SET NULL
);

In this example, we add two options to the "Orders" table. The ON DELETE CASCADE option indicates that when a user record is deleted, all order records associated with the user will also be deleted. ON UPDATE SET NULL option indicates that when updating user records, the user ID in the order record will be set to a Null value.

How to use foreign keys?

In database programming, you can use some basic commands to query tables containing foreign keys. Here is some sample code:

-- 查找所有订单
SELECT * FROM order;

-- 查找用户John的所有订单
SELECT * FROM order WHERE user_id=(SELECT user_id FROM user WHERE username='John');

In both examples, we query the "Orders" table that contains foreign key constraints. In the second example, we also used a nested query to export order records for a specific user.

When you perform a modification operation, make sure that the modification does not violate foreign key constraints. If the modified value does not satisfy the foreign key constraints, an error will occur. If this occurs, modify the appropriate data to satisfy the foreign key constraints. Here is an example:

-- 修改订单，将John的所有订单修改为Jane的订单
UPDATE order SET user_id=(SELECT user_id FROM user WHERE username='Jane') WHERE user_id=(SELECT user_id FROM user WHERE username='John');

In this example, we modify all orders belonging to user "John" to orders belonging to user "Jane". Before doing this, make sure user "Jane" already exists.

Conclusion

MySQL's foreign key constraints are very important to ensure the integrity of database relationships. This article provides examples to help you create and use foreign key constraints. Since foreign key constraints are one of the features of the MySQL engine, you must choose the correct engine when creating a table. Properly setting foreign key constraints will help improve the stability and maintainability of the data table.

The above is the detailed content of mysql foreign key settings. For more information, please follow other related articles on the PHP Chinese website!