Foreign key constraints in MySQL database

Anyone who has developed a database-driven small web application using MySQL knows that creating, retrieving, updating, and deleting tables in a relational database is a simple process. In theory, as long as you have mastered the usage of the most common SQL statements and are familiar with the server-side scripting language you have chosen, it is enough to cope with the various operations required for the MySQL table,

Anyone who has developed a database-driven small web application using MySQL knows that creating, retrieving, updating, and deleting tables in a relational database is a simple process. In theory, as long as you have mastered the usage of the most common SQL statements and are familiar with the server-side scripting language you have chosen, it is enough to cope with the various operations required for the MySQL table,

Anyone who has developed a database-driven small web application using MySQL knows that creating, retrieving, updating, and deleting tables in a relational database is a simple process. In theory, as long as you have mastered the usage of the most common SQL statements and are familiar with the server-side scripting language you have chosen, it is enough to cope with the various operations required for the MySQL table, especially when you use the quick MyISAM database engine. However, even in the simplest circumstances, things are much more complicated than we think. The following is a typical example. Assume that you are running a blog website, which is updated almost every day and allows visitors to comment on your post.

In this case, our database model should include at least two MyISAM tables, one for storing your blog articles and the other for processing comments from visitors. Obviously, there is a one-to-many relationship between the two tables, so we need to define a foreign key in the second table so that the database integrity can be maintained when updating or deleting data.

For applications like above, maintaining the integrity of both tables is a serious challenge, and the biggest difficulty is that we must maintain their integrity at the application level. This is the method most web projects that do not require transactions to take during development, because MyISAM tables provide excellent performance.

Of course, this is also costly. As I mentioned earlier, applications must maintain database integrity and consistency, this means implementing more complex programming logic to process the relationships between tables. Although database access can be simplified by using the abstraction layer and ORM module, the logic required to process the data tables required by applications will become more and more complex as the number of data tables required by applications increases.

Is there a database-level foreign key processing method for MySQL to help maintain database integrity? Fortunately, the answer is yes! MySQL also supports InnoDB tables, so that we can 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.

There are both advantages and disadvantages. 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 of a newer version of MySQL also supports foreign key constraints.

This article describes how to apply a foreign key constraint to an InnoDB table. In addition, we will use a simple PHP-based MySQL abstract class to create relevant sample code. Of course, you can also use other server-side languages you like. Now we will introduce how to apply foreign key constraints to MySQL.

Time to use foreign key constraints

To be honest, when using InnoDB tables in MySQL, it is not necessarily not a foreign key constraint. However, for the purpose of foreign key constraints, the code of the example above is described in detail. It includes two MyISAM tables for storing blog articles and comments respectively.

When defining the database mode, we need to establish a one-to-many relationship between the two tables by creating a foreign key in the table where the comment is stored, to map the data rows (that is, comments) to a specific blog post. The following is the basic SQL code for creating an example 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 constitute the data layer of the blog application. As you can see, the first table is named blogs, which consists of a number of fields with obvious meanings. It is used to store the ID, title, and content of each blog article, and finally the author. The second table named comments is used to store comments related to various blog articles. It uses the blog article ID as its foreign key to establish a one-to-many relationship.

So far, our work has been quite easy, because we just created two simple MyISAM tables. Next, we need to fill these tables with some records to further demonstrate the operations that should be performed in the other table When deleting table items in the first table.

Update and maintain database integrity

In the previous section, we created two MyISAM tables to act 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 enter some records in these tables by using the SQL command, as shown below:

 
 

  
  
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 code above actually simulates comments made by readers Susan and Rose on our first blog. Suppose we want to use another article to update the first blog. Of course, this situation may happen.

In this case, in order to maintain Database Consistency, the comments table must also be updated, either manually or by processing data layer applications. In this example, we will use the SQL command to complete the update, as shown in the following code:

 
 

  
  
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 above, because the content of the data item in 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 the server language.

To complete this operation, PHP can use a simple sub-process. However, if a foreign key constraint is used, the update operation on the comments table can be delegated to the database.

As mentioned above, InnoDB MySQL tables provide seamless support for this function. Therefore, we will use the foreign key constraint to re-run the previous sample code.

Cascade database updates

Next, we will use the foreign key constraints and InnoDB tables (instead of the default MyISAM type) to recreate the previous sample code. To do this, you must first redefine the two sample tables so that they can use specific database engines. Therefore, you can use the following SQL code:

 
 

  
  
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; 
 
 

The code here differs significantly from the previous Code in that the two tables now use the InnoDB Storage engine, so they can support foreign key constraints. In addition, pay attention to the Code for defining 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 blog_id value of the foreign key of the comments table is also updated. In other words, what we do here is to make MySQL maintain database integrity in a cascade manner, which means that when a blog is updated, the comments connected to it should also immediately reflect this change, it is important that the implementation of this function is not completed at the application layer.

The two sample 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 above, we do not need to update the comments table because MySQL will automatically process all of this. In addition, when trying to UPDATE the data rows of the blogs table, you can also remove the "on update" section of the query or specify "no action" and "RESTRICT" to make MySQL do nothing. Of course, you can also ask MySQL to do other things, which will be introduced separately in subsequent articles.

Through the above introduction, I think you have a clear understanding of how to use the foreign key constraints in the InnoDB table in MySQL. Of course, you can also write the code in the out-of-the-box, to further deepen the understanding of this convenient database function.