Definition and limitations of foreign keys in MySQL database

Title: Definition and Limitations of Foreign Keys in MySQL Database

In the MySQL database, a foreign key is a constraint used to establish relationships between tables. Foreign keys ensure that data in one table is related to data in another table. By defining foreign keys, we can achieve referential integrity between data to ensure data consistency and reliability.

1. Definition of foreign keys

In MySQL, foreign keys can be defined through the following syntax:

CREATE TABLE table name (
    column name data type,
    Foreign key column name data type,
    FOREIGN KEY (foreign key column name) REFERENCES related table name (related column name)
);

Among them, foreign key column name is the column name that needs to be associated in the current table, associated table name is the table name of the table that needs to be associated, Associated column name is the column name that needs to be associated in the associated table. With this definition, we can establish a one-to-many relationship between tables.

2. Restrictions on foreign keys

When defining foreign keys, we can add some restrictions to ensure the integrity of the data. The following are some common foreign key constraints:

• ON DELETE CASCADE: When a record in the master table is deleted, the related records in the slave table will also be deleted.
• ON DELETE SET NULL: When a record in the main table is deleted, the foreign key value of the related record in the slave table will be set to NULL.
• ON UPDATE CASCADE: When the primary key value of a record in the master table is updated, the foreign key value of the related record in the slave table will also be updated.
• ON UPDATE SET NULL: When the primary key value of a record in the master table is updated, the foreign key value of the related record in the slave table will be set to NULL.

The following is an example of specific foreign key definitions and restrictions:

CREATE TABLE parent table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE subtable (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent table (id) ON DELETE CASCADE
);

In this example, a one-to-many relationship is established between the parent table and the child table. When a record in the parent table is deleted, the related records in the child table will also be deleted. This ensures data integrity and consistency.

Through the above introduction, we can see that in the MySQL database, the definition and restrictions of foreign keys are very important. It can help us establish the relationship between tables and effectively manage and maintain it. data in the database.

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