Foreign key constraints in MySQL

In this article, we will learn how to use a basic abstract class in PHP 5 to update data in two InnoDB tables using foreign key constraints. The following example shows how to use the foreign key constraint in the script language on the server.

I. Update MySQL Data Using Foreign key constraints

Currently, the most popular open-source relational database management system is not MySQL, and MySQL supports multiple storage engines. The default storage engine is MyISAM. For many readers, it may have been used for a long time before developing a database-driven web application.

However, sometimes our project may require additional features, such as foreign key constraints, so we need to use other types of MySQL storage engines. In this case, the InnoDB table will be very suitable for our requirements, although it may be slightly inferior to the MyISAM table in terms of performance. As you know, one of the main advantages of using the InnoDB table foreign key constraint is that it enables us to process and maintain the relationship between multiple tables at the database level, this task does not need to be pushed to certain modules or libraries of applications dealing with these tables.

Of course, in the previous articles, we have already introduced the foreign key constraints of the IndoDB table, but they are all manually operating the foreign key constraints. In this article, we will explain how to trigger cascade update and delete operations for corresponding sub-tables using the script language when updating and deleting data in the parent table.

Here, the data layer of our blog application consists of two tables. In the previous example, operations on these tables are performed by manually typing SQL commands. Now, we will introduce how to use the PHP programming language to complete these tasks. PHP is selected because it is currently the most common MySQL collocation language. Next we will take PHP 5 as an example to illustrate how to operate two InnoDB tables with foreign key constraints. By reading this article, you will learn more about the features of foreign key constraints.

Now, let's begin to witness the power brought by the combination of PHP 5 and foreign key constraints!

2. Update and delete data in the database in a cascading manner

Let's review what we have learned before. Previously, we introduced how to use foreign key constraints to cascade updates and delete data in the InnoDB table storing blog post comments. It doesn't matter if you haven't read the previous article. Let's review the content below.

The definitions of the two tables used in our example are as follows:

 

Code highlighting produced by Actipro CodeHighlighter (freeware)
Http://www.CodeHighlighter.com/

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,

Primary 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,

Primary key ('id '),

KEY 'blog _ ind '('blog _ id '),

CONSTRAINT 'comments _ ibfk_1 'foreign key ('blog _ id') REFERENCES 'blogs' ('id') ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = InnoDB default charset = utf8;
The code above defines two tables. Note the second one because it specifies a constraint for the "blog_id" field. Therefore, when the data in the post table is updated and deleted, the corresponding cascade operation is triggered.

To help you understand this process, we can fill in some data in the table. At this time, we can use the SQL statement INSERT, as shown below:

 

Code highlighting produced by Actipro CodeHighlighter (freeware)
Http://www.CodeHighlighter.com/

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, Tom), (NULL, 1, Commenting first blog entry, Rose)
Now, the only blog data has two comment data. If you need to update the blog and comment data for any reason, run the following command: