Detailed explanation of foreign key constraints in MySQL database

[Introduction] Anyone who has developed a small database-driven web application using MySQL knows that creating, retrieving, updating, and deleting tables in a relational database are relatively simple processes. In theory, as long as you master the usage of the most common SQL statements and become familiar with the server-side scripts you choose to use. Anyone who has developed a small database-driven web application using MySQL knows that the tables of a relational database Creating, retrieving, updating, and deleting operations are relatively simple processes. Theoretically, as long as you master the usage of the most common SQL statements and are familiar with the server-side scripting language you choose to use, it is enough to handle the various operations required on MySQL tables, especially when you use the fast MyISAM database engine. when. But even in the simplest cases, things are more complicated than we think. Below we use a typical example to illustrate. Suppose you are running a blog site that you update almost daily, and the site allows visitors to comment on your posts.

In this case, our database schema should include at least two MyISAM tables, one for storing your blog posts and the other for processing visitor comments. Obviously, there is a one-to-many relationship between these two tables, so we need to define a foreign key in the second table so that the integrity of the database can be maintained when data rows are updated or deleted.

For an application like the one above, not only is maintaining the integrity of the two tables a serious challenge, but the biggest difficulty is that we must maintain their integrity at the application level. . This is the approach taken during development for most web projects that do not require the use of transactions because MyISAM tables provide excellent performance.

Of course, this also comes at a cost. As I said earlier, the application must maintain the integrity and consistency of the database, which means implementing more complex programs. Design logic to handle relationships between various tables. Although database access can be simplified through the use of abstraction layers and ORM modules, as the number of data tables required by an application increases, the logic required to handle them will undoubtedly become more complex.

So, for MySQL, is there any database-level foreign key processing method to help maintain database integrity? Fortunately, the answer is yes! MySQL can also support it InnoDB tables allow us to handle foreign key constraints in a very simple way. This feature allows us to trigger certain actions, such as updating and deleting certain data rows in the table to maintain predefined relationships.

Everything has pros and cons. The main disadvantage of using InnoDB tables is that they are slower than MyISAM, especially in large-scale applications where many tables must be queried. obvious. Fortunately, the MyISAM table in the newer version of MySQL also supports foreign key constraints.

This article will introduce how to apply foreign key constraints to InnoDB tables. In addition, we will use a simple PHP-based MySQL abstract class to create the relevant sample code; of course, you can also use your favorite other server-side language. Now, we start introducing how to apply foreign key constraints to MySQL.

　

The timing of using foreign key constraints

To be honest, when using an InnoDB table in MySQL, it is not necessary to use foreign key constraints. , however, in order to understand the utility of foreign key constraints in certain situations, we will specifically illustrate it through the code of the example mentioned earlier. It includes two MyISAM tables, used to store blog posts and comments.

When defining the database schema, we need to establish a one-to-many relationship between the two tables by creating a foreign key in the table where the comments are stored to separate the data rows (i.e. comments ) corresponds to a specific blog post. Here is the basic SQL code to create a sample MyISAM table:

DROP TABLE IF EXISTS `test`.`blogs`;

CREATE TABLE `test`.`blogs` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`title` TEXT,

`content` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`comments`;

CREATE TABLE `test`.`comments` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`blog_id` INT(10) UNSIGNED DEFAULT NULL,

`comment` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET= utf8;

Above, we just defined two MyISAM tables, which form the data layer of the blog application. As you can see, the first table is called blogs. It consists of some obvious fields, which are used to store the ID, title and content of each blog post, and finally the author. The second table is named comments, which is used to store comments related to each blog post. It uses the ID of the blog post as its foreign key to establish a one-to-many relationship.

So far, our work has been relatively easy, because we have only created two simple MyISAM tables. Next, what we want to do is populate these tables with some records to further demonstrate what should be done in the other table when an entry is deleted in the first table.

Update and maintain the integrity of the database

In the previous part, we created two MyISAM tables to serve as the data layer of the blog application. Of course, the above introduction is still very simple, and we need to discuss it further. To do this, we will populate these tables with some records by using SQL commands as follows:

INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Ian')

INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, 'Commenting first blog entry', 'Susan Norton'), (NULL, 1, 'Commenting first blog entry', 'Rose Wilson')

The above code actually simulates readers Susan and Rose's first blog entry for us. Blogger made a comment. Suppose now we want to update the first blog with another post. Of course, this situation is possible.

In this case, in order to maintain the consistency of the database, the comments table must also be updated accordingly, either manually or by an application processing the data layer. . For this example, we will use SQL commands to complete the update, as shown below:

UPDATE blogs SET id = 2, title = "Title of the first blog entry", content = 'Content of the first blog entry', author = 'John Doe' WHERE id = 1

UPDATE comments SET blog_id = 2 WHERE blod_id = 1

As mentioned before, because the first blog The content of the data item has been updated, so the comments table must also reflect this change. Of course, in reality, this update operation should be completed at the application layer rather than manually, which means that this logic must be implemented using a server-side language.

In order to complete this operation, PHP can go through a simple sub-process, but in fact, if foreign key constraints are used, the update operation on the comments table is completely Can be delegated to the database.

As mentioned earlier in the article, InnoDB MySQL tables provide seamless support for this function. Therefore, in the later part we will use foreign key constraints to re-create the previous example code.

　Cascade update of database

Below, we will restructure the previous example code using foreign key constraints and InnoDB tables (instead of the default MyISAM type). To do this, first redefine the two sample tables so that they can use a specific database engine. To do this, you can use SQL code like this:

DROP TABLE IF EXISTS `test`.`blogs`;

CREATE TABLE `test`.`blogs` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`title` TEXT,

`content` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`comments`;

CREATE TABLE `test`.`comments` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`blog_id` INT(10) UNSIGNED DEFAULT NULL,

`comment` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`),

KEY `blog_ind` ( `blog_id`),

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Compared with the previous code, one obvious difference between the code here and the previous code is that these two tables now use the InnoDB storage engine, so they can support foreign key constraints. In addition, we also need to pay attention to the code that defines the comments table:

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

Actually, this statement notifies MySQLMySQL that when the blogs table is updated, the value of the foreign key blog_id in the comments table should also be updated. In other words, what is done here is to let MySQL maintain database integrity in a cascading manner. This means that when a blog is updated, the comments connected to it must also immediately reflect this change. It is important. The important thing is that the implementation of this function is not completed at the application layer.

The two example MySQL tables have been defined. Now, updating these two tables is as simple as running an UPDATE statement, as shown below:

　"UPDATE blogs SET id = 2, title = "Title of the first blog entry", content = 'Content of the first blog entry', author = 'John Doe' WHERE id = 1"

As mentioned before, we don’t need to update the comments table because MySQL will handle it all automatically. Additionally, you can have MySQL do nothing when trying to update a row in the blogs table by removing the "ON UPDATE" part of the query or specifying "NO ACTION" and "RESTRICT". Of course, you can also let MySQL do other things, which will be introduced in subsequent articles.

Through the above introduction, I think everyone has a clear understanding of how to use foreign key constraints in conjunction with InnoDB tables in MySQL. Of course, you can also further write the upcoming code to further develop your understanding of this convenient database feature.

The above is the detailed content of Detailed explanation of foreign key constraints in MySQL database. For more information, please follow other related articles on the PHP Chinese website!