The disadvantage of "Mysql" foreign key cascade and Cascade

When you are building a table, you can set up cascading for deleting delete and modifying update. The concept of a foreign-key cascade Cascade is illustrated with an example

If there is a usertable in the database as follows:

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

Below I will create a new cascade_test table as follows, where user_id and Usertable's primary key ID form referential integrity, and simultaneously establish a cascade of deletions and modifications:

If a table is created with 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 Usertable's primary key is an integer UNSIGNED, this must be the same type, 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 to the normal setting of the foreign key statement, under which there are more than two cascade declarations, on the DELETE CASCADE, on UPDATE CASCADE

There is no difference in referential integrity between setting cascading and not setting cascading.

User ID type is how cascade_test user_id type should be, here need to strictly copy, because the user's ID is set to unsigned shaping, here cascade_test user_id can not be plastic, must be deliberately declared as unsigned shaping , otherwise the following [ERR] 1005-can ' t create table ' xx ' (errno:150), in fact, 150 page is referential integrity error.

After that, insert some of the following data into the newly created cascade_test:

Similarly, due to the existence of referential integrity, the value range of the user_id of the cascade_test also needs to be taken in the existing value of the user ID.

The only difference between setting cascading and not setting a cascade is that we delete all the relevant fields of Cascade_test's user_id while deleting the data in the user's ID. Modifying the Cascade is also true.

For example, delete the item with ID 6 in user, that is, execute the following statement:

Delete from usertable where id=6

Operating effects such as:

Usertable the id=6 item is deleted is yes,

Because of the presence of a delete cascade: user_id=6 items in cascade_test are all removed.

If you do not set a cascade, items that are id=6 in usertable are deleted, and the items that are user_id=6 in Cascade_test are not deleted. resulting in the absence of a certain referential integrity.

Here are the disadvantages of cascading:

This seems to be very powerful, especially in the Web programming, you do not have to write so many delete statements, just delete a primary key, you can delete all the items involved

However, when your project is sufficient, such deletions can be slow, essentially creating a coupling between the table and the table. It is far better than not to delete the new add a Isdel item, use the flag to delete the way, look up the table when only the Isdel=false item is queried. In fact, the DELETE statement in the Web page programming time is simply can be thrown away. This also has the advantage of being able to find the old data that has been deleted and can definitely be found back.

This way your page will run much faster, or if you do cascade DELETE statements, the tables involved will be enough to execute slowly enough.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The disadvantage of "Mysql" foreign key cascade and Cascade