InnoDB FOREIGN Key Usage Summary

 Use' Wfc_database '; # Main Table (also known as: Referenced table, referencedTable, outtable)ALTER TABLE' App ' ENGINE=innodb;# from a table (also known as: Reference table, appearance, referencingTable)ALTER TABLE' App_version ' ENGINE=innodb;# an "app" can have multiple "app versions" # So apps and App_version are1: N Relationship (one app_id corresponds to multiple av_app_id) # app_id is the primary key of the app table, av_app_id is the index of the App_version table # App_version data correction (delete a in app_version table) PP has no app_id)DELETE  from' App_version 'WHEREav_app_id not inch(SELECTapp_id fromapp); # This step is extremely important, otherwise the foreign key cannot be added (the foreign key ID that is not in the primary table in the table is not allowed) # app_version add foreign keyALTER TABLE' App_version 'ADD CONSTRAINTfk_av_app_idFOREIGN KEY(av_app_id)REFERENCES' app ' (app_id) on DELETE CASCADE  on UPDATE CASCADETo establish a foreign key premise: the data type of the constrained field and foreign key must be the same as the constrained field needs to be set to index, the foreign key needs to be set toPRIMARYforeign key action: make two tables associative, foreign keys can only reference values from columns in a table! Specify from table keywords:Foreign Key(column name) refers to the foreign key keyword:References <Foreign key table name>(foreign key column name) # Event Trigger (Cascade operation) Limit: on DeleteAnd on Updatecan be set parametersCascade(following foreign key changes), strongly recommended, able to save data consistencyRestrict(Restrict foreign key changes in the primary table)[default]No Action # If the above statement has been reported as following error in the creation process Can not Create Table 'd91. #sql -197e_18b4'(errno: Max# Please set the field type of av_app_id and app_id to be exactly the same as the type of the constraint field, the length, the unsigned, whether it is empty, the default value to set the same as the foreign key for this scenario, I set the app_id and av_app_id toINT(Ten), UNSIGNED, not NULLMake sure the foreign key name does not exist and that the key value already exists/Duplicate Index name # Add if you want to delete a foreign key constraint you can do thisAlter TableTable nameDrop Foreign Keyforeign KEY constraint name; # For example, to remove the foreign key you just created, you canALTER TABLE' App_version 'DROP FOREIGN KEYfk_av_app_id; #---------------------InnoDB foreign key Knowledge-------------------------#method One: Define a data table if a computer manufacturer, its database holds the machine and accessories product information.　　The table used to store the product information is called a Pc, and the table used to store the parts supply information is called parts.　　There is a field in the PC table that describes the CPU model used by the computer;　　There is a field in the parts table that describes exactly the CPU model, and we can think of it as a list of all CPU models. Obviously, the manufacturer of the computer, its use of the CPU must be the supply information table (parts) exists in the model. At this point, there is a constraint relationship in two tables (constraintthe CPU models in the--PC table are constrained by the models in the Parts table. Let's start by creating the parts table:CREATE TABLEParts (... Field definition ..., modelVARCHAR( -) not NULL,... field definition ...); Next is the PC table:CREATE TABLEPC (..... Field definition ..., CpumodelVARCHAR( -) not NULL,...　　field definition ...}; Set the index to set the MySQL foreign key in the reference table[appearance](ReferencingTable, i.e. PC table) and referenced table[Main Table](ReferencedTable, which is the parts table), the corresponding two fields must all be indexed (Index). On the Parts table:ALTER TABLEPartsADD INDEXIdx_model (model);　　To add an index to the Parts table, the index is built on the model field, and the call is named Idx_model. Also similar to PC tables:ALTER TABLEPcADD INDEXIdx_cpumodel (Cpumodel); In fact, these two indexes can be set when the table is created.　　Here just to highlight its necessity. Define a foreign key below for two tables to establish the type of "constraint" described earlier. Because the CPU model of the PC must refer to the corresponding model in the Parts table, we set the Cpumodel field of the PC table to "foreign key" (FOREIGN KEY), which is the reference value of the key from the other table. ALTER TABLEPcADD CONSTRAINTFk_cpu_modelFOREIGN KEY(Cpumodel)REFERENCESparts (model);　　The first line is to set up a MySQL foreign key for the PC table, give the foreign key a name called Fk_cpu_model; the second line is to set the Cpumodel field of the table as a foreign key; the third line is that the foreign key is constrained by the model field of the Parts table. In this way, our foreign key is OK.　　If we try to create a PC, it uses a CPU model that does not exist in the parts table, then MySQL prevents the PC from being created. Cascading operations Consider the following scenario: The technician found that the one-month-old number of CPUs (and possibly many) of a series that were entered into the parts table had a wrong letter and now needs to be corrected. What we want is that when the parts table of those referencedColumnChanges, the referencing in the corresponding tableColumncan also be corrected automatically. This keyword can be added at the end of the MySQL foreign key definition: on UPDATE CASCADE, that is, when the main table is updated, the child tables (we) produce a chain update action, it seems that some people like to call this "cascade" operation.　　:) If this statement is written in its entirety, it is: # example oneALTER TABLEPcADD CONSTRAINTFk_cpu_modelFOREIGN KEY(Cpumodel)REFERENCESParts (model) on UPDATE CASCADE; ExceptCASCADEOutside, there areRESTRICT(Prohibit primary table changes),SET NULL(the child table corresponding field is set to empty) and so on. Extended Read FOREIGN key (Foreign KeyIf the public keyword is the primary key in a relationship, then this common keyword is called the foreign key of the other relationship. Thus, the foreign key represents the connection between the two relationships. A table with a foreign key of another relationship as the primary key is called the primary table, and a table with a key in it is called the primary table from the table.　　Foreign keys are also referred to as foreign keywords. The role of foreign keys: to maintain data consistency, integrity, the main purpose is to control the data stored in the External key table. Make two tables associative, and foreign keys can only refer to the values of columns in the table! Method Two: The precondition for establishing a foreign key: the column of this table must be the same as the foreign key type (the foreign key must be the outer primary key). Foreign key effect: make two tables to form an association, foreign key can only reference the value of the column in the outside! Specify the PRIMARY key keyword:Foreign Key(column name) refers to the foreign key keyword:References <Foreign key table name>Event Trigger limit (foreign key column name): onDelete and onUpdate, can be set parameter cascade (following the foreign key changes),Restrict(Restricting foreign key changes in the appearance),Set Null(set null value),Set Default(set default value),[default]No action for example: Outtable table primary key ID typeintto create a table that contains a foreign key:Create Table Temp(IDint, nameChar( -),Foreign Key(ID)Referencesouttable (ID) on Delete Cascade  on Update Cascade); To modify a table after creation:Alter Table Temp Add constraint  Foreign Key(ID)ReferencesOuttable (ID) on Delete Cascade  on Update CascadeDescription: Set the ID column as a foreign key, referring to the ID column of the outer outtable, when the value of the foreign key deletes the corresponding column in this table, and when the value of the foreign key changes the corresponding column value in this table. In the use of ALTER, constraint is for use with foreign keys. This cascade operation of the primary foreign key only supports the InnoDB type in MySQL. To set the change to this type in the configuration file in MySQL default does not support InnoDB support, you can enter the command to see if support: MySQL>Show variables like"Has%"; If you do not support stopping MySQL, open the My.ini configuration file to find the Skip-InnoDB, front add #, restart MySQL, and then modify the primary foreign key two table types: MySQL> Alter TableXXX ENGINE=InnoDB; Finally, enter the method above to create the foreign key in the command. However, if your business does not need to use transactions, then using MyISAM is the best consideration because MyISAM does not support transactions and has better performance. However, if your business has to use transactions, that is, the data consistency requirements are very high, you need to use to Inodb, because Inodb to use the lock, so its concurrency is poor, so the performance is also poor. If you want to delete a foreign key constraint, you can use the following command: MySQL> Alter TableSs_accesscodeDrop Foreign Keyforeign KEY constraint name; Note: If you do not specify a FOREIGN KEY constraint name when you add a foreign key constraint, the system automatically adds a FOREIGN KEY constraint name: Table name _ibfk_n (represents the nth foreign KEY constraint). For example, we omit the foreign key harness name when we create the foreign key. 

InnoDB FOREIGN Key Usage Summary