How to use MySQL FOREIGN key constraint settings

If Table A's primary key is a field in table B, the field is called the Foreign key of Table B, table A is called the primary table, and table B is called from the table. Foreign keys are used to achieve referential integrity, and different foreign key constraints will allow the two tables to be tightly combined, especially if the modified or deleted cascade operation will make routine maintenance easier. Foreign key is mainly used to ensure the integrity and consistency of data two tables must be InnoDB table, MyISAM table temporarily does not support foreign key column must be indexed, MySQL4.1. 2 later versions will automatically create indexes when foreign keys are created, but if they need to be displayed in an earlier version, the columns of the two tables in the foreign key relationship must be of similar data types, that is, columns that can be converted to types, such as int and tinyint, and int and Char cannot be ; Create foreign KEY syntax: code to copy code as follows[CONSTRAINT [Symbol]]FOREIGN KEY[index_name](Index_col_name, ...)REFERENCEStbl_name (index_col_name,...)[On DELETE reference_option][On UPDATE reference_option]reference_option:RESTRICT | CASCADE | SET NULL |no action if the child table attempts to create a foreign key value that does not exist in the parent table, InnoDB rejects any insert or update operations. If the parent table attempts to update or delete any foreign key values that exist or match in any of the child tables, the final action depends on the on update and on delete options in the FOREIGN key constraint definition. InnoDB supports 5 different actions, if not specified on delete or onUPDATE, the default action is restrict:1.CASCADE: Deletes or updates the corresponding row from the parent table, and automatically deletes or updates the matching rows from the table. on DELETECanscade and onUPDATECanscade are supported by InnoDB. 2.SET NULL: Deletes or updates the corresponding row from the parent table, and sets the foreign key column in the child table to be empty. Note that these foreign key columns are not set to NOT NULL when they are valid. on DELETE SETNull and onUPDATE SET SETNULL is supported by InnoDB. 3.  NO Action:innodb refuses to delete or update the parent table. 4.RESTRICT: Refuses to delete or update the parent table.  specifying Restrict (or no ACTION) is the same as ignoring the on delete or the on Update option. 5.SET DEFAULT: InnoDB not currently supported. FOREIGN KEY constraints use the most two cases:1when the parent table is updated, the child table is also updated, and when the parent table is deleted, the deletion fails if the child table has a matching item;2when the parent table is updated, the child table is also updated, and the parent table is deleted when the child table matches the item. In the previous case, in the foreign key definition, we use the onUPDATE CASCADE  on DELETE RESTRICTin the latter case, you can use the onUPDATE CASCADE  on DELETE CASCADE. Use case: (1Create a table: code to copy code as follows----http://www.111cn.net Original-- CREATE TABLE IF  not EXISTS' article ' (' ID ')int( One) not NULLauto_increment, ' category_id 'int( One) not NULL, ' name 'Char( -) not NULL,PRIMARY KEY(' id '),KEY' Fk_1 ' (' category_id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 auto_increment=2 ; INSERT  into' article ' (' id ', ' category_id ', ' name ')VALUES(1,1,'Article 1'); CREATE TABLE IF  not EXISTS' category ' (' ID ' )int( One) not NULLauto_increment, ' name 'Char( -) not NULL,PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 auto_increment=2 ; INSERT  into' Category ' (' id ', ' name ')VALUES(1,'Category 1'); Create a FOREIGN KEY constraint: code to copy code as followsALTER TABLE' article 'ADD CONSTRAINT' Fk_1 'FOREIGN KEY(' category_id ')REFERENCES' category ' (' id ');2) Delete the data from the Main Table category:Delete  from' Category 'WHEREId=1, will error: #1451 -CannotDelete or UpdateA parent Row:aForeign Key constraintFails (' Test '. ' article ',CONSTRAINT' Fk_1 'FOREIGN KEY(' category_id ')REFERENCES' category ' (' ID ')) (3From table article, add a category_id that does not exist:Insert  intoArticle (Category_id,name)Values(2,'Category 2') will error: #1452 -CannotAdd or UpdateA child row:aForeign Key constraintFails (' Test '. ' article ',CONSTRAINT' Fk_1 'FOREIGN KEY(' category_id ')REFERENCES' category ' (' ID ')) (4change the update deletion constraint code as follows to copy code--Delete foreign keyALTER TABLEArticleDROP FOREIGN KEYFk_1--Add foreign KeyALTER TABLE' Article 'ADD CONSTRAINT' Fk_1 'FOREIGN KEY(' category_id ')REFERENCES' category ' (' id ') on DELETE CASCADE  on UPDATE CASCADEHere's what to do: code to copy code like this--the records in article are also deletedDelete  fromCategorywhereId=1;--at this point, the category_id in article will also be updated to 3UPDATE' Test '. ' Category 'SET' ID '= '3' WHERE' Category '. ' ID '=2;

How to use MySQL FOREIGN key constraint settings