A detailed explanation of the foreign key constraint problem in Mysql _mysql

People who have developed database-driven small web applications with MySQL know that creating, retrieving, updating, and deleting tables for relational databases is a simpler process. In theory, as long as you have the most common use of SQL statements and familiarize yourself with the server-side scripting language you choose to use, you'll be able to handle the various operations you need for the MySQL table, especially if you're using a fast MyISAM database engine. But even in the simplest case, things are much more complicated than we think. Here we use a typical example to illustrate. Suppose you are running a blog site that you update almost every day, and that site allows visitors to comment on your posts.

MySQL FOREIGN KEY constraint conditions

The foreign key constraints of MySQL are as follows:

· CASCADE: Automatically deletes or updates matching rows in a child table when a row is deleted or updated from the parent table.
· Set NULL: Automatically sets the foreign-key column value for the child table to NULL when the row is deleted or updated from the parent table. If the corresponding foreign key column does not specify a NOT NULL qualifier.
· No action: In the ANSI SQL-92 standard, the No action means no action is taken.
· RESTRICT: Deny delete or update operations to the parent table.

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

Applications like the one above, not only maintain the integrity of the two tables is a serious challenge, and the biggest difficulty is that we must maintain their integrity at the application level. This is the approach that most Web projects that do not require the use of transactions during development, because the MyISAM table can provide excellent performance.

Of course, there is a cost to doing this, as I said earlier, the application must maintain the integrity and consistency of the database, which means that more complex program design logic is implemented to handle the relationships between the tables. While it is possible to simplify database access by using abstraction layers and ORM modules, the logic needed to process them becomes increasingly complex as the number of data tables required by the application increases.

So, for MySQL, is there a database-level foreign key approach to help maintain database integrity? Luckily, the answer is YES! MySQL can also support innodb tables, allowing 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 a table to maintain a predefined relationship.

The main disadvantage of using InnoDB tables is that they are slower than MyISAM, especially in large applications that have to query many tables. Fortunately, the newer version of MySQL's MyISAM table also supports foreign key constraints.

This article describes how to apply a foreign key constraint to a InnoDB table. 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, let's start with how to apply a foreign key constraint to MySQL.

　　Time to use FOREIGN key constraints

To be honest, using the InnoDB table in MySQL is not necessarily a foreign key constraint, however, for the purpose of the foreign key constraint in some cases, we will use the example above to specify the code. It consists of two MyISAM tables, which are used to store blog posts and comments, respectively.

When defining a database schema, we create a one-to-many relationship between the two tables by creating a foreign key in the table where the comments are placed to correspond the data rows (that is, comments) to a specific blog post. The following is the basic SQL code to create the sample MyISAM table:

　　

DROP TABLE IF EXISTS ' test '. ' Blogs ';

CREATE TABLE ' test '. ' Blogs ' (

' id ' INT () UNSIGNED auto_increment,

' title ' text, '

content ' text,

' Author ' VARCHAR () default NULL,

prirose KEY (' id ')

) engine=myisam default Charset=utf8;


DROP TABLE IF EXISTS ' test '. ' Comments ';

CREATE TABLE ' test '. ' Comments ' (

' id ' int () UNSIGNED auto_increment,

' blog_id ' int (a) UNSIGNED DEFAULT NULL ,

' comment ' TEXT,

' author ' VARCHAR () 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, named blogs, consists of a number of clearly defined fields that store the ID, title, and content of each blog post, and finally the author. The second table, named comments, is used to store comments about blog posts, and it creates a one-to-many relationship by using the ID of the blog post as its foreign key.

So far, our work has been easy because we've just created two simple MyISAM tables. Next, we're going to use some records to populate these tables to further demonstrate that when you delete a table entry in the first table, you should perform those actions in another table.

　　Update and maintain the integrity of the database

In the previous section, we created two MyISAM tables to act as the data tier for the blog application. Of course, the above introduction is very simple, we need to do a further discussion. To do this, we'll fill in some records in these tables by using the SQL command, as shown in the following example:

　　

INSERT into blogs (ID, title, content, author) VALUES (NULL, ' title of the ' The ' the ' of the ' the ', ' the ' the ' of the ' the ', ' the ' Try ', ' Ian ')

INSERT into comments (ID, blog_id, comment, author) VALUES (NULL, 1, ' commenting-A-blog entry ', ' Susa n Norton '), (NULL, 1, ' Commenting-entry ', ' Rose Wilson ')

The code above actually simulates the reader Susan and Rose commenting on our first blog post. Let's say we're going to use another article to update the first blog post. Of course, this situation is likely to happen.

In this case, to maintain database consistency, the comments table must be updated either manually or through an application that processes the data tier. For the purposes of this example, we will use the SQL command to complete the update, as shown in the following:

UPDATE Blogs SET id = 2, title = "title of the The" the "the" the "the", content = ' content of the ' the ' the ' of the ' the ', author = ' J Ohn Doe ' WHERE id = 1

UPDATE comments SET blog_id = 2 WHERE blod_id = 1

As mentioned earlier, because the content of the first blog's data item has been updated, the comments table must also reflect this change. Of course, in reality this update should be done at the application level, not by hand, which means that the logic must be implemented using a server-side language.

In order to do this, you can use a simple subroutine for PHP, but in fact, if you use a FOREIGN KEY constraint, the update operation on the comments table is entirely delegated to the database.

As the article says earlier, the InnoDB MySQL table provides seamless support for this feature. So, later, we'll use the foreign key constraint to recreate the previous example code.

Cascading updates for Databases

Next, we will reconstruct the previous sample code using the FOREIGN KEY constraint and the 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 the SQL code shown below:

　　

DROP TABLE IF EXISTS ' test '. ' Blogs ';

CREATE TABLE ' test '. ' Blogs ' (

' id ' INT () UNSIGNED auto_increment,

' title ' text, '

content ' text,

' Author ' VARCHAR () default NULL,

prirose KEY (' id ')

) engine=innodb default Charset=utf8;


DROP TABLE IF EXISTS ' test '. ' Comments ';

CREATE TABLE ' test '. ' Comments ' (

' id ' int () UNSIGNED auto_increment,

' blog_id ' int (a) UNSIGNED DEFAULT NULL ,

' comment ' TEXT,

' author ' VARCHAR () 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=in Nodb DEFAULT Charset=utf8;

The obvious difference between the code here and the previous code is that the two tables now use the InnoDB storage engine, so they can support foreign key constraints. In addition, we also need to note 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 is the notification mysqlmysql, when the blogs table is updated, also update the comments table foreign key blog_id value. In other words

Say, what's done here is to let MySQL maintain database integrity in a cascading way, which means that when a blog is updated, the annotation associated with it immediately responds to the change, and it is important that the implementation of this feature is not done at the application level.

Two examples of MySQL tables have been defined, and now updating these two tables is as simple as running an UPDATE statement, as follows:

"UPDATE Blogs SET id = 2, title =" title of the "the" the "the" the "entry", content = ' content of the ' the ' the ' of the ' the ', author = ' John Doe ' WHERE id = 1 '

As we said earlier, we don't need to update the comments table, because MySQL will automatically handle all of this. In addition, when trying to update rows of data in a blogs table, you can also make MySQL do nothing by removing the "On Update" section of the query or by stipulating "no ACTION" and "RESTRICT". Of course, there are other things that can be done with MySQL, which will be described separately in subsequent articles.

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