MySQL FOREIGN key cascade Update Delete

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 ' 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.

MySQL FOREIGN key cascade Update Delete