MySQL Cascade delete Update

First of all, currently in the product environment available MySQL version (referring to 4.0.x and 4.1.x), only the InnoDB engine allows the use of foreign keys, so our data table must use the InnoDB engine.

Below, we first create the following test database tables:

 
 create table  ' ROOTTB '   (
 
    ' id '  int (one)  unsigned auto_ Increment not null, 
 
    ' data '  varchar ( not null default)   ', 
 
   PRIMARY KEY  (' id ') 
 
)  type=innodb; 
 
 
 
 create table  ' SUBTB '   (
 
    ' id '  int (one)  unsigned auto _increment not null, 
 
    ' Rootid '  int (one)  UNSIGNED NOT NULL  default  ' 0 ', 
 
    ' data '  varchar (+)  NOT NULL DEFAULT  ', 
 
   PRIMARY KEY  (' id '), 
 
   INDEX  (' Rootid '), 
 
   foreign  KEY  (' Rootid ')  REFERENCES ROOTTB (' id ')  on delete cascade 
 
)   Type=innodb; 
 
 

Attention:
1, must use the InnoDB engine;
2, foreign keys must be indexed (index);
3, foreign key binding relationship here "on DELETE CASCADE" is used, meaning that if the foreign key corresponding data is deleted, the associated data is completely deleted, more information please refer to the MySQL manual on the InnoDB document;

OK, then we'll insert the test data:

INSERT into ' ROOTTB ' (' id ', ' data ')

VALUES (' 1 ', ' Test root Line 1 '),

(' 2 ', ' Test root Line 2 '),

(' 3 ', ' Test root Line 3 ');



INSERT into ' SUBTB ' (' id ', ' rootid ', ' data ')

VALUES (' 1 ', ' 1 ', ' Test sub line 1 for Root 1 '),

(' 2 ', ' 1 ', ' Test sub line 2 for Root 1 '),

(' 3 ', ' 1 ', ' Test sub line 3 for Root 1 '),

(' 4 ', ' 2 ', ' Test sub line 1 for Root 2 '),

(' 5 ', ' 2 ', ' Test sub line 2 for Root 2 '),

(' 6 ', ' 2 ', ' Test sub line 3 for Root 2 '),

(' 7 ', ' 3 ', ' Test sub line 1 for Root 3 '),

(' 8 ', ' 3 ', ' Test sub line 2 for Root 3 '),

(' 9 ', ' 3 ', ' Test sub line 3 for Root 3 ');

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

mysql>; Show tables;

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

| Tables_in_test |

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

| ROOTTB |

| SUBTB |

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

2 rows in Set (0.00 sec)



mysql>; SELECT * from ' ROOTTB ';

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

| ID | Data |

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

| 1 | Test Root Line 1 |

| 2 | Test Root Line 2 |

| 3 | Test Root Line 3 |

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

3 rows in Set (0.05 sec)



mysql>; SELECT * from ' SUBTB ';

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

| ID | Rootid | Data |

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

|      1 | 1 | Test sub Line 1 for Root 1 |

|      2 | 1 | Test sub Line 2 for Root 1 |

|      3 | 1 | Test sub Line 3 for root 1 |

|      4 | 2 | Test sub Line 1 for Root 2 |

|      5 | 2 | Test sub Line 2 for Root 2 |

|      6 | 2 | Test sub Line 3 for root 2 |

|      7 | 3 | Test sub Line 1 for Root 3 |

|      8 | 3 | Test sub Line 2 for Root 3 |

|      9 | 3 | Test sub Line 3 for Root 3 |

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

9 rows in Set (0.01 sec)

Well, it's all right, OK, let's test our cascade delete feature.

We will only delete the data record with ID 2 in the ROOTTB table, and see if the related child records Rootid 2 in the SUBTB table are automatically deleted:

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

Query OK, 1 row affected (0.03 sec)



mysql>; SELECT * from ' ROOTTB ';

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

| ID | Data |

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

| 1 | Test Root Line 1 |

| 3 | Test Root Line 3 |

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

2 rows in Set (0.00 sec)



mysql>; SELECT * from ' SUBTB ';

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

| ID | Rootid | Data |

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

|      1 | 1 | Test sub Line 1 for Root 1 |

|      2 | 1 | Test sub Line 2 for Root 1 |

|      3 | 1 | Test sub Line 3 for root 1 |

|      7 | 3 | Test sub Line 1 for Root 3 |

|      8 | 3 | Test sub Line 2 for Root 3 |

|      9 | 3 | Test sub Line 3 for Root 3 |

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

6 rows in Set (0.01 sec)

Well, it's true that the corresponding data in the SUBTB table is automatically deleted and the test is successful.

Conclusion: The use of foreign keys in MySQL to achieve cascading delete success!
---------------------------------------------------------------------------------------------------------------
User table:
CREATE TABLE User
(
UserID integer NOT NULL auto_increment primary key,
Username varchar () NOT NULL
)
Type=innodb;

Password table:
CREATE TABLE Password
(
UserID integer NOT NULL,
Password varchar () NOT NULL,
Index (userid),
FOREIGN KEY (userid) references User (UserID)
ON DELETE Cascade
ON UPDATE cascade
)
Type=innodb;

1. mysql supports foreign key constraints and provides the same functionality as other DB, but the table type must be InnoDB
2. The column of the table that built the foreign key to add index.

MySQL Cascade delete Update