Original Cascade Delete and update using foreign keys in MySQL

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 that the parent table must have a corresponding index, and the child table will automatically create the corresponding index when the foreign key is created.
When you create an index, you can specify the corresponding action on the child table when the parent table is deleted, updated,
Includes restrict, NO ACTION, SET null, and Cascade. where restrict and no action are the same,
The parent table cannot be updated when the child table is associated with a 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 NULL when 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 DELETE CASCADE on UPDATE CASCADE

) Engine=innodb DEFAULT charset=latin1;

Attention:
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.

Author: Little Fei Source: http://www.cnblogs.com/little-fly/Welcome reprint also Please keep this statement thank you!

Original Cascade Delete and update using foreign keys in MySQL