search
HomeDatabaseMysql TutorialDetailed explanation of foreign key constraints in MySQL database

Detailed explanation of foreign key constraints in MySQL database

Mar 19, 2017 am 10:30 AM
phpphp tutorialVideo tutorial

[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. In theory, as long as you master the usage of the most common SQL statements and become familiar with the server-side scripts you choose to use. Anyone who has developed a small database-driven web application using MySQL knows that the tables of a relational database Creating, retrieving, updating, and deleting operations 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 daily, and the 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 processing 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 one above, not only is maintaining the integrity of the two tables a serious challenge, but the biggest difficulty is that we must 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 programs. Design logic to handle 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 it InnoDB tables allow 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 the table to maintain predefined relationships.

Everything has pros and cons. 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. obvious. 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.

 

The timing of using foreign key constraints

To be honest, when using an InnoDB table in MySQL, it is not necessary to use foreign key constraints. , however, in order to understand the utility of foreign key constraints in certain situations, we will specifically illustrate it through the code of the example mentioned earlier. 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 the comments are stored to separate the data rows (i.e. comments ) corresponds to a specific blog post. 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 work has been relatively 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.

Update and maintain the integrity of the database

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')

The above code actually simulates readers Susan and Rose's first blog entry for us. Blogger made a comment. 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 the consistency of the database, the comments table must also be updated accordingly, either manually or by an application processing the data layer. . For this example, we will use SQL commands to complete the update, 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

UPDATE comments SET blog_id = 2 WHERE blod_id = 1

As mentioned before, because the first blog The content of the data item has been updated, so 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 go through a simple sub-process, but in fact, if foreign key constraints are used, the update operation on the comments table is completely Can be delegated to the database.

As mentioned earlier in the article, InnoDB MySQL tables provide seamless support for this function. Therefore, in the later part we will use foreign key constraints to re-create the previous example code.

 Cascade update of database

Below, we will restructure the previous example code using foreign key constraints and InnoDB tables (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 this:

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;

Compared with the previous code, one 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

Actually, 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. It is important. The important thing is that the implementation of this function is not completed 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 don’t need to update the comments table because MySQL will handle 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 the upcoming code to further develop your understanding of this convenient database feature.

The above is the detailed content of Detailed explanation of foreign key constraints in MySQL database. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools