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 copy Code as follows [CONSTRAINT [symbol]] FOREIGN Key[index_name] (index_col_name, ...) REFERENCES tbl_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. The INNODB supports 5 different actions, and if no on delete or on UPDATE is specified, 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 DELETE Canscade and on UPDATE Canscade 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 set null and on UPDATE set set NULL are 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 is 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 former case, in the foreign key definition, we use on update CASCADE on delete RESTRICT; in the latter case, you can use on Update 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 NULL auto_increment, ' category_id 'int( One) not NULL, ' name 'Char( -) not null,primary key (' ID '), key ' Fk_1 ' (' category_id ') ENGINE=innodb DEFAULT Charset=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 NULL auto_increment, ' name 'Char( -) not null,primary KEY (' id ')) ENGINE=innodb DEFAULT Charset=utf8 auto_increment=2 ; INSERT into ' category ' (' id ', ' name ') VALUES (1,'Category 1'Create a FOREIGN KEY constraint: The code below copies the code alter TABLE ' article ' ADD CONSTRAINT ' fk_1 ' FOREIGN KEY (' category_id ') REFERENCES ' category ' ( ' id '); (2Delete the data from the Main Table category: Delete from ' category ' WHERE id=1, will error: #1451-cannot delete or update a parent ROW:A FOREIGN KEY constraint fails (' Test '. ' article ', constraint ' fk_1 ' foreign key (' category_id ') REFERENCES ' category ' (' ID ')) (3From table article, add Category_id:insert into article (Category_id,name) values that do not exist (2,'Category 2') will error: #1452-cannot add or update a child row:a FOREIGN KEY constraint fails (' Test '. ' article ', constraint ' fk_1 ' foreign key (' C ategory_id ') REFERENCES ' category ' (' ID ')) (4change the update deletion constraint code as follows to copy code--Delete foreign key alter TABLE article DROP FOREIGN key Fk_1--add foreign key alter TABLE ' article ' Add CONSTRAINT ' Fk_1 ' FOREIGN KEY (' category_id ') REFERENCES ' category ' (' id ') on DELETE C Ascade on UPDATE Cascade This is done as follows: code to copy Code--the records in article are also deleted Delete 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