-
This article will introduce readers to foreign key constraints in MySQL. Through the introduction of this article, you will find that it is easy to trigger cascade updates to child table data when updating data rows in the parent table.
This article will introduce readers to foreign key constraints in MySQL. Through the introduction of this article, you will find that it is easy to trigger cascade updates to child table data when updating data rows in the parent table.
1. 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. 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 every day, and the site allows visitors to comment on your posts.
In this case, our database schema should include at least two MyISAM tables, one to store your blog posts and another to handle 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 above, not only is maintaining the integrity of the two tables a serious challenge, but the biggest difficulty is that we have to 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 programming logic to handle the 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 InnoDB tables, allowing us to use a very simple way to handle foreign key constraints. 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, and 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. 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.
2. When to use foreign key constraints
To be honest, when using InnoDB tables in MySQL, you do not necessarily have to use foreign key constraints. However, for the purpose of foreign key constraints in certain situations, we will use the code of the previously mentioned example to explain in detail. 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 comments are stored to map the data rows (i.e. comments) to a specific blog. article. 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 job has been 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.
3. Update blog posts and maintain database integrity
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') above The code actually simulates the situation where readers Susan and Rose commented on our first blog post. 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 database consistency, the comments table must also be updated accordingly, either manually or by an application processing the data layer. For this example, we will use a SQL command to complete the update as follows:
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 earlier, because the content of the data item of the first blog has been updated, 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 use a simple sub-process, but in fact, if foreign key constraints are used, the update operation of the comments table can be delegated to the database.
As mentioned earlier in the article, InnoDB MySQL tables provide seamless support for this functionality. Therefore, in the later part we will use foreign key constraints to re-create the previous example code.
4. Cascade update of database
Below, we will restructure the previous example code using foreign key constraints and an InnoDB table (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 the following:
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; An 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 In fact, 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. What is important is the implementation of this function. It's not done 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 do not need to update the comments table , because MySQL handles 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 immediate code to further deepen your understanding of this convenient database function. understanding.
5. Summary
In this article, we detail the basics of using foreign key constraints with InnoDB tables in MySQL. As you can see in the examples in this article, it is easy to trigger cascading updates to child table data items when the contents of the parent table are updated, and it also illustrates how applications that handle the data layer can be exempted from the need to do so. implementation of this feature. Of course, we can also provide the same cascading effect when the parent table deletes data rows, which we will explain in a later article.