Summary of InnoDB foreign key usage and InnoDB Key Usage

Summary of InnoDB foreign key usage and InnoDB Key Usage

USE 'wfc _ database'; # ALTER table 'app' ENGINE = INNODB for the master TABLE (also known as the referenced table, referenced TABLE, and outTable; # ALTER table 'app _ version' ENGINE = INNODB from a TABLE (also called a reference table, External TABLE, and referencing table; # One [application] can have multiple [Application versions] # therefore, the relationship between the app and app_version is 1: n (one app_id pair should have multiple av_app_id) # app_id is the primary key of the app table, and av_app_id is the index of the app_version table # app_version data correction (delete the app_id that the app does not have in the app_version table) delete from 'app _ version' WHERE av_app_id not in (SELECT app_id FROM app); # This step is extremely important; otherwise, a foreign key cannot be added (the foreign key id that does not exist in the master table is not allowed FROM the table) # app_version ADD the foreign key alter table 'app _ version' add constraint fk_av_app_id foreign key (av_app_id) REFERENCES 'app_id) on delete cascade on update cascade; # prerequisites for creating a foreign key: the data type of the restricted field must be the same as that of the foreign key. The restricted field must be set to an index, and the foreign key must be set to a PRIMARY foreign key: associate two tables. The foreign key can only reference the values of columns in the table! Specify the slave table Keyword: foreign key (column name) Reference foreign key Keyword: references <foreign key table name> (foreign key column name) # event triggering (cascade operation) restriction: the on delete and on update parameters can be set to cascade (with foreign key changes). We strongly recommend that you store data consistency restrict (restrict foreign key changes in the master table) [Default] no action # If the preceding statement reports the following error during creation: Can not create table 'd91. # sql-197e_18b4 '(errno: 150) # Set the Field Types of av_app_id and app_id to be exactly the same, that is, the type, length, symbols, null, and default value of the constraint field to be the same as that of the foreign key. For this scenario, I set both app_id and av_app_id to INT (10), UNSIGNED, and NOT NULL to ensure that the foreign key name does NOT exist or already exists. Key value/index name of # supplement, if you want to delete the foreign key constraint, you can do this alter table Name drop foreign key constraint name; # For example, to delete the created foreign key, you can alter table 'app _ version' drop foreign key fk_av_app_id; # --------------------- InnoDB foreign key knowledge ----------------------- # Method 1: Define a data TABLE if a computer manufacturer, its database stores the product information of the entire machine and accessories. The table used to save the product information of the entire machine is called Pc; the table used to save the accessory supply information is called Parts. In the Pc table, there is a field used to describe the CPU model used by the computer. In the Parts table, there is a field describing the CPU model, we can think of it as a list of all CPU models. Obviously, the CPU used by the computer produced by this manufacturer must be the model existing in the supply information table (parts. In this case, two tables have a constraint. The CPU model in the Pc table is subject to the Model constraints in the Parts table. First, CREATE a parts TABLE: create table parts (... field definition ..., model VARCHAR (20) not null ,... field definition ...); next is the Pc TABLE: create table pc (... field definition ..., cpumodel VARCHAR (20) not null ,... field definition ...}; to set the index, if you want to set the MySQL foreign key, refer to the reference table [External table] (referencing table, that is, Pc table) and the referenced table [primary table] (referenced table, that is, parts table) the corresponding two fields must be indexed ). For Parts tables: alter table parts add index idx_model (model); this statement means that an INDEX is added to the parts TABLE and the INDEX is created on the model field, name the index idx_model. Similar to Pc tables: alter table pc add index idx_cpumodel (cpumodel); in fact, these two indexes can be set during TABLE creation. This is just to highlight its necessity. Define the "constraint" described above for the two tables under the foreign key ". 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 ), that is, the reference value of this key comes from other tables. Alter table pc add constraint fk_cpu_modelFOREIGN KEY (cpumodel) REFERENCES parts (model); the first line is to set a MySQL foreign KEY for the Pc TABLE. Name this foreign KEY fk_cpu_model; the second row sets the cpumodel field of the table as a foreign key. The third row indicates that the foreign key is restricted by the model field of the Parts table. In this way, we can use the foreign key. If we try to CREATE a Pc, and the CPU model used does not exist in the Parts table, MySQL will disable this PC from being created. Cascade operations consider the following situation: technicians found that all models of a series of CPUs (which may have many models) entered into the parts table a month ago all entered a wrong letter, now you need to correct it. We hope that when the Referenced columns in the parts table change, the Referencing Column in the corresponding table can also be automatically corrected. When defining the MySQL foreign key, you can add the keyword on update cascade at the end. That is, when the primary table is updated, the sub-tables (WHO) generate a chain UPDATE action, it seems that some people like to call this cascade operation. :) If the statement is completely written, it is: # Example 1: alter table pc add constraint fk_cpu_modelFOREIGN KEY (cpumodel) REFERENCES parts (model) on update cascade; besides CASCADE, you can also perform operations such as RESTRICT (change of the master table is prohibited) and set null (the corresponding field of the sub-table is SET to NULL. If a public Key is a primary Key in a link, this public Key is called the Foreign Key of another link. The foreign key represents the relationship between two links. A table with a foreign key as the primary keyword is called a master table, and a table with another key is called a slave table of the master table. A foreign key is also called a foreign key. Foreign key: maintains data consistency and integrity. The main purpose is to control the data stored in the foreign key table. Associate two tables. The foreign key can only reference the values of columns in the External table! Method 2: Create a foreign key: the columns in the table must be of the same type as the foreign key (the foreign key must be the external primary key ). Foreign key: Associate two tables. A foreign key can only reference the values of columns in the External table! Specify the primary key Keyword: foreign key (column name) Reference foreign key Keyword: references <foreign key table name> (foreign key column name) event trigger restrictions: on delete and on update, configurable parameters include cascade, restrict, set Null, set Default ), [Default] no action, for example: create table temp (id int, name char (20), foreign key (id) references outTable (id) on delete cascade on update cascade); Modify the table after creation: alter table temp add constraint foreign key (id) references outTable (I D) on delete cascade on update cascade; Note: Set the id column as a foreign key and delete the corresponding columns in this table based on the id column of the external outTable; when the foreign key value changes, the corresponding column value in this table is changed. In alter, constraint is used for foreign keys. This cascade operation of the primary and Foreign keys only supports the InnoDB type in mysql. To set this type to be changed in the mysql configuration file, InnoDB is not supported by default. You can run the following command to check whether: mysql> show variables like "have % "; if you cannot stop mysql, open my. in the ini configuration file, locate skip-innodb, add # in front of it, restart mysql, and modify the two table types of the primary and Foreign keys: mysql> alter table xxx ENGINE = InnoDB; finally, enter the method for creating the foreign key in the command. However, if your business does not need to use transactions, myisam is the best consideration, because myisam does not support transactions and has better performance. However, if your business needs to use transactions, that is to say, if you have high requirements on data consistency, you need to use INODB. Because INODB needs to use locks, its concurrency capability is somewhat lower, therefore, the performance may be poor. To delete a foreign key constraint, run the following command: mysql> alter table ss_accesscode drop foreign key constraint name. Note: If no foreign key constraint name is specified when a foreign key constraint is added, the system automatically adds the foreign key constraint name: Table Name _ ibfk_n (representing the nth foreign key constraint ). For example, the name of the foreign key harness is omitted when a foreign key is created.

 

Spring + hibernate + mysql can only use innodb of mysql to set foreign keys for cascading deletion?

Create table SC (
Sno CHAR (8 ),
Foregin key (Sno) references Student (Sno)
);

Note that MySQL Foreign keys can only be used in InnoDB tables. This is critical because the default MyISAM is generally used.

 
How can I start the innoDB Engine in MySQL so that I can set foreign key constraints?

Change the engine of the default table: alter table name engine = innodb;