Detailed explanation of the role and usage of foreign keys in MySQL database
In the MySQL database, foreign keys are an important tool used to establish associations between tables. It can Ensures data integrity and provides a way to maintain relationships between tables. This article will introduce in detail the role and usage of foreign keys in the MySQL database and provide specific code examples.
1. The role of foreign keys
2. Usage of foreign keys
CREATE TABLE table name ( column name data type, ... FOREIGN KEY (foreign key column name) REFERENCES parent table name (parent table column name) [ON DELETE CASCADE/SET NULL/RESTRICT/NO ACTION] [ON UPDATE CASCADE/SET NULL/RESTRICT/NO ACTION] );
Among them, FOREIGN KEY
is used to define foreign keys, REFERENCES
specifies the parent table and parent table columns, ON DELETE
and ON UPDATE
is used to specify the operation strategy for deletion and update.
ALTER TABLE
statement to modify the table structure. The syntax is as follows: ALTER TABLE subtable name ADD CONSTRAINT foreign key name FOREIGN KEY (foreign key column name) REFERENCES parent table name (parent table column name);
ALTER TABLE
statement to delete foreign keys. The syntax is as follows: ALTER TABLE subtable name DROP FOREIGN KEY foreign key name;
3. Specific code examples
The following uses a specific example to demonstrate the use of foreign keys in the MySQL database:
Suppose we have two tables, one is The student table (students
) and the other is the grades table (scores
) have a one-to-many relationship between them. We require that the student ID in the student grade table must exist in the student table.
CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(50) );
CREATE TABLE scores ( score_id INT PRIMARY KEY, student_id INT, score INT, FOREIGN KEY (student_id) REFERENCES students(student_id) );
In the above example, we defined the student_id
column in the grades table as a foreign key, which is associated with the student_id
column in the student table. In this way, when inserting grade data, the system will automatically check the validity of the student ID.
Summary:
The foreign key in the MySQL database is a very important data relationship tool. It can ensure data integrity, establish relationships between tables, and constrain data. operate. Proper use of foreign keys can improve database operation efficiency and data consistency, which is an indispensable part of database design. I hope this article will help you understand the role and usage of foreign keys in the MySQL database.
The above is the detailed content of Detailed explanation of the role and usage of foreign keys in MySQL database. For more information, please follow other related articles on the PHP Chinese website!