"Python" Django data model, cascade Delete, cascade Update, ER diagram export, etc.

In this article, we'll show the reader in detail how to trigger cascading updates and deletions on child table data while updating and deleting the parent table data. You'll see that this process can be easily done with the help of a foreign key constraint when using the InnoDB table.

  I. Using FOREIGN KEY constraints to update and delete data in MySQL

We know that developing a database- driven application that can maintain the integrity of multiple tables is a complex matter-even if the application is facing the current most popular open source relational database management system MySQL server . If an application has to handle multiple database tables, and there are some predefined relationships between the tables, when the data in the parent table is updated or deleted, the changes must be correctly reflected in the child table, or there will be many problems.

For MySQL, in most cases, database integrity issues like this can be solved by using the library ORM, but this is not the only way to solve the problem. Another solution is to use the MySQL InnoDB storage engine's foreign KEY constraint. When using this engine, we can have the child table perform the specified action to respond when the parent table performs actions such as update and delete.

In the previous article, we demonstrated how to trigger a cascade delete of the corresponding data in a table that holds comments about a blog from a parent table when you delete data from it.

The following example illustrates how to maintain the integrity of the table at the database level, rather than pushing the task to the application that handles the data tier.

Before we introduce a FOREIGN key constraint in MySQL's InnoDB table, we trigger cascading updates or cascade deletions individually, and in fact, when the parent table's keys are updated and deleted at the same time, we can trigger the corresponding actions on the child table at the same time, which makes it easier to maintain database consistency.

We will cover this in more detail below.

  II. Deleting data in cascade mode

In order to maintain continuity, we will still use the previous example when we describe how to update and delete the data in a cascading way. Before learning something new, let's review how to use FOREIGN key constraints to delete data from a data table that stores comments when a particular blog post is deleted, and note that this involves only the delete operation.

The following is a definition of the two tables used in our example:

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

CREATE TABLE ' test '. ' Blogs ' (

' ID ' INT (Ten) UNSIGNED auto_increment,

' Title ' TEXT,

' Content ' TEXT,

' Author ' VARCHAR DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8;


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

CREATE TABLE ' test '. ' Comments ' (

' ID ' INT (Ten) UNSIGNED auto_increment,

' blog_id ' INT (Ten) UNSIGNED DEFAULT NULL,

' Comment ' TEXT,

' Author ' VARCHAR DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' Blog_ind ' (' blog_id '),

CONSTRAINT ' Comments_ibfk_1 ' FOREIGN KEY (' blog_id ') REFERENCES ' blogs ' (' id ') on DELETE CASCADE

) Engine=innodb DEFAULT Charset=utf8;

In the above code, we define two simple InnoDB tables, the first one to store the blog data, and the second to save comments about the blog. Obviously, there is a one-to-many relationship between the two tables, which can be used to demonstrate the benefits of foreign key constraints. Now, populate our table with the data shown below:

INSERT into blogs (ID, title, content, author) VALUES (NULL, ' title of the first blog entry ', ' content of the first blog en Try ', ' Tom ')

INSERT into Comments (ID, blog_id, comment, author) VALUES (null, 1, ' Commenting first blog entry ', ' Susan Norton '), (null , 1, ' Commenting first blog entry ', ' Rose ')

Okay, now there's data in the table. But how do you remove the first data item from the blogs table outside the application hierarchy? In fact, this is simple, as shown in the following command:

DELETE from blogs WHERE id = 1

If we define a simple foreign key constraint, then the delete command above will not only delete the first blog, but also all the comments associated with it will be emptied, and this process can be done in a single step, hehe, sounds good.

However, as mentioned earlier in this article, the InnoDB storage engine also allows cascading updates and deletions to be performed at the same time, which we'll cover for the reader in detail below.

  III, the use of extended foreign key constraints

It is now time to introduce how to cascade updates and deletions of data in a child table when the parent table data is deleted, which can effectively simplify the logical implementation of the application that handles these tables.

To help you better understand this feature provided by the InnoDB storage engine, we'll illustrate this with an example. Now, we redefine the two tables we've seen before and specify that when a particular blog is updated and deleted, the corresponding cascade action is performed on the table comments. The definitions of the two tables are given below:

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

CREATE TABLE ' test '. ' Blogs ' (

' ID ' INT (Ten) UNSIGNED auto_increment,

' Title ' TEXT,

' Content ' TEXT,

' Author ' VARCHAR DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8;



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

CREATE TABLE ' test '. ' Comments ' (

' ID ' INT (Ten) UNSIGNED auto_increment,

' blog_id ' INT (Ten) UNSIGNED DEFAULT NULL,

' Comment ' TEXT,

' Author ' VARCHAR DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' Blog_ind ' (' blog_id '),

CONSTRAINT ' Comments_ibfk_1 ' FOREIGN KEY (' blog_id ') REFERENCES ' blogs ' (' ID ') on the DELETE CASCADE on UPDATE CASCADE

) Engine=innodb DEFAULT Charset=utf8;

As shown above, the first table of the defined blog is the same as the previous one, so we just need to pay attention to the second table. In this case, the field for table comments remains the same, except that this time it contains the SQL statement as follows:

CONSTRAINT ' Comments_ibfk_1 ' FOREIGN KEY (' blog_id ') REFERENCES ' blogs ' (' ID ') on the DELETE CASCADE on UPDATE CASCADE

Of course, this is responsible for blog updates and deletions when it comes to cascading updates and deletions of comments.

We have assigned a constraint to the foreign key blog_id, and now the integrity of the relationship between the two tables above can be handled entirely at the database level, although there may be some loss in performance for some applications. We'll show you how to do this easily.

  Iv. practical examples of FOREIGN key constraints

Earlier, we have defined two INDODB tables and used them as building blocks for the blog application. Now, all we have to do is update and delete all the comments that correspond to the blog whenever there is a blog update and deletion.

We will demonstrate it through specific code. So, assuming that the only blog data stored in the blogs table needs to be updated, the comments must be updated at the same time, and we can do this with an UPDATE statement, 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 = ' J Ohn Doe ' WHERE id = 1

As you may have guessed, an update to the first blog entry will automatically cause the comments that are related to that blog to be updated. Now, let's delete the blog using the SQL query shown below:

DELETE from blogs WHERE id = 2

At this point, MySQL will delete the comments for us. Now we have seen the help that foreign key constraints bring to us in maintaining the consistency of relationships across multiple tables. Is it very convenient? Wait for what, you also try it!

  V. Summary

In this article, we give the reader a detailed description of how to trigger cascading updates and deletions on child table data while updating and deleting the parent table data. As you can see, the process is easily handled with the help of a foreign key constraint when using the InnoDB table.

It should be noted that, so far, the operation of the sample database tables, they are manually through the SQL command, however, in a web-based environment, you need to use some kind of server -side language to deal with MySQL. PHP is a good choice, so we'll discuss how to use foreign KEY constraints with PHP 5 in the next article.

Django data model, ER diagram, cascading operationsThe Django model field types Summary-devil_2009 's Column-Blog channel-csdn.netUnderstanding Django's many-to-many Manytomanyfield-mwi-iteye technology web siteNAVICAT11 full series activation (keygen)-PinterestNavicat Customizing view of the ER diagram and export table structure of a few tables-queenjade-Blog channel-csdn.netWhat is the difference between Onetoonefield and ForeignKey in the Django model _ Baidu knowsRookie Django ForeignKey advice-Python communityModel Field Reference | Django Documentation | DjangoPlay the update and delete of FOREIGN key constraints in MySQL-admin126com-chinaunix blogUsing foreign keys in MySQL to cascade Delete, UPDATE-dodott column-Blog channel-csdn.net

"Python" Django data model, cascade Delete, cascade Update, ER diagram export, etc.