How to set foreign keys in mysql

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 one-to-many relationship between these two tables, that is, one user can have multiple orders, and one order belongs to only one user.

Foreign key is a relational database term that is a constraint that joins two tables. Foreign key constraints ensure that when referencing data in another table, an instance must be created with a value that matches the 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: When specifying a parent item, the value of the child item will be deleted or updated to be Null.

• 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 have added 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');

We will change all orders belonging to the "John" user to orders belonging to the "Jane" user, for reference. Before doing this, make sure user "Jane" already exists.

The above is the detailed content of How to set foreign keys in mysql. For more information, please follow other related articles on the PHP Chinese website!