[Mysql] the disadvantage of external key cascade and cascade, mysql key-level disadvantage

[Mysql] the disadvantage of external key cascade and cascade, mysql key-level disadvantage

When creating a table, you can set delete and update to cascade. An example is used to describe the concept of cascade of Foreign keys.

Assume that a usertable exists in the database as follows:

This user table is very simple and id is the primary key.

The following is a new cascade_test table. Here, the user_id and the primary key id of the usertable form the integrity of the reference, and create a cascade of deletion and modification:

If a table is created using an SQL statement, it is as follows:

Create table 'test '. 'cascade _ test' ('id' integer unsigned not null auto_increment, 'content' TEXT, 'user _ id' integer unsigned, -- because the primary key of usertable is integer unsigned, the same type must be used here. The KEY value corresponds to primary key ('id'), CONSTRAINT 'fk _ cascade_test_1 'foreign key' FK _ cascade_test_1 '('user _ id ') REFERENCES 'test '. 'usertable' ('id') on delete cascade on update cascade) ENGINE = InnoDB; -- database ENGINE settings, can not

Compared with general statements that set foreign keys, there are two more CASCADE declarations under them, on delete cascade, ON UPDATE CASCADE

There is no difference in the integrity of the reference when you set cascade and do not set cascade.

The user_id type of cascade_test should be the same as the user_id type of user, which must be copied strictly here. Because the user id is set to unsigned integer, The user_id of cascade_test cannot be an integer here, be sure to specifically declare it as an unsigned integer, otherwise the following [Err] 1005-Can't create table 'xx' (errno: 150) will appear ), in fact, the 150 page is a reference integrity error.

Insert the following data in the newly created cascade_test:

Similarly, because of the integrity of the reference, the value range of user_id of cascade_test also needs to be obtained from the existing value of user id.

The only difference between setting cascade and not setting cascade is that when we delete the data in the user id, all related fields of user_id of cascade_test will also be deleted. The same is true for modifying cascading.

For example, you can delete the item id 6 in the user, that is, execute the following statement:

delete from usertable where id=6

The running effect is as follows:

The id = 6 item in usertable is definitely deleted,

Because the cascade operation is deleted, the user_id = 6 item in cascade_test is deleted.

If cascade is not set, the user_id = 6 item in cascade_test will not be deleted when the item id = 6 in usertable is deleted. This causes a lack of integrity of the reference.

The following describes the disadvantages of cascade:

This seems very powerful, especially when programming on the web page, you don't need to write so many Delete statements. You just need to delete a primary key to delete all the items involved.

However, when your project is sufficient, such deletion will be slow, which actually results in coupling between tables. It is far more than adding an isDel item without deleting a new one. You can use the marker to delete the item. You can only query the isDel = false items when querying the table. In fact, the delete statement can be discarded during web page programming time. In this case, you can find the deleted old data.

In this way, the running speed of Your webpage will be greatly accelerated. Otherwise, if you execute the cascade deletion statement, the execution will be slow enough when there are enough tables involved.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.