Mysql uses foreign keys to implement cascading deletion and updating methods _mysql

This article illustrates how MySQL uses foreign keys to implement cascading deletes and updates. Share to everyone for your reference, specific as follows:

MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires that the parent table must have a corresponding index, and the child table automatically creates the corresponding index when the foreign key is created. When you create an index, you can specify the appropriate action to be performed on the child table when the parent table is deleted, updated, including restrict, NO action, SET NULL, and Cascade. Where the restrict and no action are the same, the parent table cannot be updated when the child table is associated with the record, Cascade indicates that the parent table updates or deletes the child table's corresponding record when it is updated or deleted, and SET NULL indicates that the parent table was updated or deleted. The corresponding field for the child table is set NULL.

Since only the InnoDB engine is allowed to use foreign keys, our datasheet must use the InnoDB engine. The version I am using is a Mysql5.1 version, and the process is as follows:

To create a database:

Create database test;

Create two tables, where the "id" of the first table is the foreign key of the second table (userinfo):

CREATE TABLE ' user ' (
 ' id ' int (4) NOT null,
 ' sex ' enum (' F ', ' m ') DEFAULT NULL,
  PRIMARY KEY (' id ')
) engine= InnoDB DEFAULT charset=latin1;
CREATE TABLE ' userinfo ' (
 ' sn ' int (4) NOT NULL auto_increment,
 ' userid ' int (4) is not NULL,
 ' info ' varchar DE FAULT NULL,
 PRIMARY key (' SN '),
 key ' userid ' (' userid '),
 CONSTRAINT ' userinfo_ibfk_1 ' FOREIGN key (' UserID ') REFERENCES ' user ' (' ID ') on the DELETE CASCADE on UPDATE CASCADE
) engine=innodb DEFAULT charset=latin1;

Attention:

1, the storage engine must use the InnoDB engine;

2, the foreign key must establish the index;

3. The foreign key binding relationship here uses "on delete CASCADE" "On Update CASCADE", meaning that if the foreign key corresponding data is deleted or updated, the associated data is completely deleted or updated accordingly. For more information, refer to the MySQL manual for InnoDB documentation.

OK, then we'll insert the data test:

INSERT into ' user ' (' id ', ' sex ')
 VALUES (' 1 ', ' F '), (' 2 ', ' m '), (' 3 ', ' f ');
INSERT into ' userinfo ' (' sn ', ' userid ', ' info ')
 VALUES (' 1 ', ' 1 ', ' 2005054DSF '),
       (' 2 ', ' 1 ', ' Fdsfewfdsfds '),
       (' 3 ', ' 1 ', ' gdsgergergrtre '),
       (' 4 ', ' 2 ', ' Et34t5435435werwe '),
       (' 5 ', ' 2 ', ' 435RTGTRHFGHFG '),
       (' 6 ', ' 2 ', ' ret345tr4345 '),
      (' 7 ', ' 3 ', ' Fgbdfvbcbfdgr '),
       (' 8 ', ' 3 ', ' 45r2343234were '),
       (' 9 ', ' 3 ', ' wfyhtyjtyjyjy ');

Let's take a look at the status of the current datasheet:

Mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user      |
| userinfo    |
+----------------+
2 rows in Set (0.00 sec)

Data in the user table:

Mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 1 | f  |
| 2 | m  | |
3 | f  |
+----+------+
3 rows in Set (0.00 sec)

UserInfo the data in the table:

Mysql> select * from UserInfo;
+----+--------+-------------------+
| sn | userid |
+----+--------+-------------------+
| 1 |   1 | 2005054DSF    |
| 2 |   1 | Fdsfewfdsfds   |
| 3 |   1 | Gdsgergergrtre  |
| 4 |   2 | Et34t5435435werwe |
| 5 |   2 | 435RTGTRHFGHFG  |
| 6 |   2 | ret345tr4345   |
| 7 |   3 | FGBDFVBCBFDGR   |
| 8 |   3 | 45r2343234were  |
| 9 |   3 | Wfyhtyjtyjyjy   |
+----+--------+-------------------+
9 rows in Set (0.00 sec)

For the establishment of the above, I believe it is no difficulty for everyone. OK, now we're going to test our Cascade deletion feature.

We will delete the data record with ID 2 in the user table to see if the related child record UserID 2 in the Userinf table is automatically deleted:

The delete operation was performed successfully!

mysql> Delete from ' user ' where ' id ' = ' 2 ';
Query OK, 1 row affected (0.03 sec)

Check that the user table has no data record of ID 2!

Mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 1 | f  |
| 3 | f  |
+----+------+
2 rows in Set (0.00 sec)

Then look at the UserInfo table has no UserID 2 of the 3 data records, the corresponding data is really automatically deleted!

Mysql> select * from UserInfo;
+----+--------+----------------+
| sn | userid |
+----+--------+----------------+
| 1 |   1 | 2005054DSF   |
| 2 |   1 | Fdsfewfdsfds  |
| 3 |   1 | Gdsgergergrtre |
| 7 |   3 | fgbdfvbcbfdgr |
| 8 |   3 | 45r2343234were |
| 9 |   3 | Wfyhtyjtyjyjy |
+----+--------+----------------+
6 rows in Set (0.00 sec)

The update operation is similar, because we have defined the foreign key deletion, the update operation is cascade, so we can test the data directly here.

Change the data record with the original ID of 1 in the user table to ID 4, and do the following:

mysql> Update user set id=4 where id= ' 1 ';
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

Now check to see if the data has changed in two tables:

Mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 3 | f  |
| 4 | f  |
+----+------+
2 rows in Set (0.00 sec)
mysql> select * from UserInfo;
+----+--------+----------------+
| sn | userid |
+----+--------+----------------+
| 1 |   4 | 2005054DSF   |
| 2 |   4 | Fdsfewfdsfds  |
| 3 |   4 | Gdsgergergrtre |
| 7 |   3 | fgbdfvbcbfdgr |
| 8 |   3 | 45r2343234were |
| 9 |   3 | Wfyhtyjtyjyjy |
+----+--------+----------------+
6 rows in Set (0.00 sec)

Compare the original table to find that they have indeed been updated successfully, the test completed!!! This also realizes to use the foreign key to the multiple related table to do simultaneously deletes, the update operation, thus guaranteed the data consistency.

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.