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

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 if the child table is associated with records, Cascade means that the parent table updates or deletes the child table corresponding records when updating or deleting, and SET NULL indicates that the parent table is updated or deleted. The corresponding field of the child table is set NULL.

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 ' T_user ' (

' user_id ' int (4) primary key,

' Role_name ' varchar (20),

' login_name ' varchar (20),

' login_pwd ' varchar (20),

' user_name ' varchar (20),

' Creat_time ' date

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 will delete the data record with ID 2 in the user table and see if the related child record of UserID 2 in the Userinf table is 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 cascade when 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 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 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