"Go" MySQL foreign key constraint on Delete, on update the meaning of each value

Reprint Address: http://hi.baidu.com/jxqlovejava/item/3d2cc5b5d689917c244b0920

?

Look at the on Delete property first, possibly with a value such as: No Action, Cascade,set Null, restrict property.

When the value is no action or restrict, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key and, if there is one, it is not allowed to be deleted.

When the value is cascade, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key, and if there is one, the record in the child table (that is, the table containing the foreign key) is also deleted.

When the value is set NULL, when the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so, set the foreign key value in the child table to null (this requires that the foreign key is allowed null).

On update is the same,

?
When the value is no action or restrict, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), the record is first checked for the corresponding foreign key and, if there is one, the update is not allowed.

When the value is cascade, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key and, if there is one, update the record in the child table (that is, the table that contains the foreign key).

When the value is set NULL, when the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key and, if there is one, set the foreign key value in the child table to null (but this requires that the foreign key is allowed to take null).

For example, I have two tables:

Tbluser User table:

DROP TABLE IF EXISTS ' filedb '. ' Tbluser ';

CREATE TABLE ' filedb '. ' Tbluser ' (

' UserID ' varchar (not NULL COMMENT ' primary key '),

' UserName ' varchar (+) not NULL COMMENT ' user name ',

' Usermail ' varchar (not NULL COMMENT ' user mailbox '),

' UserPassword ' varchar (not NULL COMMENT ' password '),

' Usertype ' tinyint (3) unsigned default ' 0 ' COMMENT ' defaults to 0 for normal user ',

' usercreated ' datetime DEFAULT ' 0000-00-00 00:00:00 ',

PRIMARY KEY (' UserID ')

) Engine=innodb DEFAULT Charset=utf8;

--------------------------------------------------------------------------------------------------------------- -

Tblfile file (user's file) Table:

DROP TABLE IF EXISTS ' filedb '. ' Tblfile ';

CREATE TABLE ' filedb '. ' Tblfile ' (

' FileID ' int (ten) unsigned not NULL auto_increment,

' Fileowner ' varchar () DEFAULT NULL COMMENT ' foreign key, reference user table ',

' filename ' varchar ($) Not NULL COMMENT ' file original name ',

' FilePath ' varchar ($) Not NULL COMMENT ' file storage path ',

' FileType ' varchar (ten) not NULL COMMENT ' file type ',

' Filesubject ' varchar (+) not NULL COMMENT ' file title ',

' filecreated ' datetime DEFAULT ' 0000-00-00 00:00:00 ' COMMENT ' creation time ',

PRIMARY KEY (' FileID '),

KEY ' fk_tblfile_1 ' (' Fileowner '),

CONSTRAINT ' Fk_tblfile_1 ' FOREIGN KEY (' Fileowner ') REFERENCES ' tbluser ' (' UserID ') on DELETE SET NULL on UPDATE CASCADE

) Engine=innodb auto_increment=2 DEFAULT Charset=utf8;

-------------------------------------------------------------------------------------------------

As you can see, Fileowner is the foreign key to the file table and refers to the userid of the user table. And here the FOREIGN KEY constraint is set to "on DELETE set NULL on UPDATE CASCADE"

Now the user table has records (omit other unrelated fields):

Userid

ST001

, and the file table has records

FileID Fileowner

1 ST001

So if I delete the ST001 corresponding record in the user table, then according to the on delete set null rule, Fileowner in the file table should be set to NULL, it is true after the hands-on attempt, if I change ST001 in the user table to ST003, according to the on UPDATE Cascade rules, Fileowner in the file table should be chained to ST003, and indeed so.

The use of foreign keys has a significant impact on reducing database redundancy and ensuring data integrity and consistency.

Note also that if a foreign key relationship exists between the two tables, MySQL cannot delete the table (drop table) directly, and the foreign key should be deleted before it can be deleted.