A detailed explanation of foreign KEY constraints in MySQL database

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.

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 (a) 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 (a) UNSIGNED auto_increment,

' blog_id ' INT (a) UNSIGNED DEFAULT NULL,

' Comment ' TEXT,

' Author ' VARCHAR DEFAULT NULL,

Prirose KEY (' id ')

) Engine=myisam DEFAULT Charset=utf8;

1 2 3 Next page > Full text reading tips: Try "←→" button, turn the page more convenient Oh!