The use of foreign keys in MySQL to implement cascade Delete, update

MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, requires the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index. When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including RESTRICT,NO action,SET NULL , and CASCADE. where RESTRICT and NO ACTION are the same, the parent table cannot be updated in the case of a child table associated with the record;CASCADE indicates that when the parent table is updated or deleted, Update or delete the corresponding record of the child table;set NULL indicates that the corresponding field of the child table is SET nullwhen the parent table is updated or deleted.

because only the InnoDB engine allows the use of foreign keys, our data tables must use the InnoDB engine. The version I am using is Mysql5.1 version, 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) is 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 DEFAULT 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;

Note:
1, the storage engine must use the InnoDB engine;
2, foreign keys must be indexed;
3, foreign key binding relationship this 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, please refer to the MySQL Manual for InnoDB documentation.

OK, then we'll insert the data test again:

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 data table:

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 | info |

+----+--------+-------------------+

| 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 table, I believe that there is no difficulty for everyone. OK, so we're going to test our cascade delete feature.
We'll delete the data record with ID 2 in the user table and see if the userid is 2 in the userinf table. Related sub-records are automatically deleted:

perform the delete operation successfully!

mysql> Delete from ' user ' where ' id ' = ' 2 ';

Query OK, 1 row affected (0.03 sec)

see that there are no data records with ID 2 in the User table!

mysql> SELECT * from user;

+----+------+

| id | sex |

+----+------+

| 1 | f |

| 3 | f |

+----+------+

2 rows in Set (0.00 sec)

look at the userinfo table has no userid for 2 of the 3 data records, the corresponding data is automatically deleted!

mysql> SELECT * from UserInfo;

+----+--------+----------------+

| sn | userid | info |

+----+--------+----------------+

| 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 already defined the foreign key Delete, the update operation is CASCADEwhen the table is built earlier, so we can test the data directly here.

change the data record in the User table with the original ID of 1 to ID 4and 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 go and 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 | info |

+----+--------+----------------+

| 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 can be found that they have indeed been updated successfully, testing completed!!! This also enables the use of foreign keys to a number of related tables to delete, update the operation, so as to ensure the consistency of the data.

The use of foreign keys in MySQL to implement cascade Delete, update