MySQL foreign key details, MySQL key details

MySQL foreign key details, MySQL key details

Recently, when I was studying Hibernate, I was always confused by foreign keys. Moreover, my MySQL foreign key learning is not very solid, so in order to better master Hibernate and MySQL, therefore, I searched for some blogs on the Internet to learn how to create Foreign keys and add Foreign keys for MySQL. I had a deep learning experience and it was so amazing that my mom no longer had to worry about my foreign keys!

The following is a summary of foreign key knowledge points!

InnoDB also holds foreign key constraints. The syntax for defining foreign key constraints in InnoDB looks as follows:

[CONSTRAINT symbol] foreign key [id] (index_col_name ,...)

REFERENCES tbl_name (index_col_name ,...)

[On delete {RESTRICT | CASCADE | set null | no action}]

[On update {RESTRICT | CASCADE | set null | no action}]

The foreign key definition follows the following conditions:

·All tables must be InnoDB type, TheyCannot be a temporary table.

· In the referenced table, an index is required, and the foreign key column is listed in the same order as the first column. If such an index does not exist, it must be automatically created in the reference table.

· In the referenced table, there must be an index. The referenced columns are listed in the same order as the first column.

· The index prefix of foreign key columns is not supported. One of the consequences is that BLOB and TEXT columns are not included in an external key, because the index of these columns must always contain a prefix length.

· If CONSTRAINT symbol is given, it must be unique in the database. If it is not given, InnoDB automatically creates this name.

InnoDB rejects any INSERT or UPDATE operation that tries to create a foreign key value in the sub-table but does not match the candidate key value in the parent table. A parent table has some child tables with matched rows. InnoDB performs some actions on any UPDATE or DELETE operations that attempt to UPDATE or DELETE the candidate key values in the parent table, this action depends ON the referential action specified by the on update and on detete clauses of the foreign key clause. When a user tries to delete or update a row from a parent table and has one or more matched rows in the child table, InnoDB has five options based on the action to be taken:

·CASCADE: Deletes or updates matched rows from the parent table and automatically deletes or updates the child table. On delete cascade and on update cascade are both available. Between two tables, you should not define several on update cascade clauses that take actions in the same column of the parent or child table.

·SET NULL: Delete or update rows from the parent table, and set the foreign key column in the child table to NULL. If the foreign key column does NOT specify the not null qualifier, this is unique and valid. The on delete set null and on update set null clauses are supported.

·NO ACTION: In ANSI SQL-92 standards, no action means this does not take ACTION, that is, if there is a related foreign key value in the referenced table, attempts to delete or update key values are not allowed (Gruber, Master SQL, 2000: 181 ). InnoDB rejects the deletion or update operation on the parent table.

·RESTRICT: The deletion or update of the parent table is denied. No action and RESTRICT are both the same. DELETE the on delete or on update clause. (Some database systems have an extension check, and no action is an extension check. In MySQL, foreign key constraints are checked immediately, so no action and RESTRICT are the same ).

· Set default: this action is recognized by the parser, but InnoDB rejects table definitions that contain the on delete set default or on update set default clause.

When the candidate keys in the parent table are updated, InnoDB supports the same selection. Select CASCADE. The foreign key column in the child table is set as the new value of the candidate key in the parent table. In the same way, if the column updated in the sub-Table references the foreign key in another table, update cascade.

Note that InnoDB supports foreign keys to be referenced in a table. In these cases, the sub-Table actually means the records attached to the table.

InnoDB requires an index for the external key and the referenced key so that the foreign key check can be performed quickly without a table scan. The index of the foreign key is automatically created. Compared with some old versions, indexes must be explicitly created in old versions. Otherwise, the creation of foreign key constraints will fail.

In InnoDB, the corresponding columns in the foreign key and referenced columns must have similar internal data types so that they can be compared without type conversion. The size and symbol of the integer type must be the same. The length of the string type does not need to be the same. If you specify a set null action, make sure that you have NOT declared this column as not null in the subtable.

If MySQL reports an error code 1005 from the create table statement and the error message string points to errno 150, this means that a foreign key constraint is incorrectly formed and the TABLE creation fails. Similarly, if alter table fails and points to errno 150, it means that the foreign key definition of the modified TABLE will be incorrectly formed. You can use show innodb status to display a detailed explanation of the recent InnoDB foreign key error on the server.

Note: InnoDB does not check foreign key constraints for those foreign keys or referenced key values that contain NULL columns.

Deviation from the SQL standard: if there are several rows in the parent table with the same referenced key value, then InnoDB takes the action in the foreign key check, it seems that other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint and there is a child row with several parent rows, InnoDB cannot delete these parent rows.

For records that reside in the corresponding index with foreign key constraints, InnoDB performs cascade operations by means of deep priority selection.

Deviations from SQL standards:If on update cascade or on update set null recursively updates the same table, the table is updated during CASCADE, and it acts like RESTRICT. This means that you cannot use the auto-reference on update cascade or on update set null operation. This will prevent the infinite loop caused by cascade updates. ON the other hand, a self-referenced on delete set null is possible, just like a self-referenced on delete cascade. Cascade operations cannot be nested for more than 15 layers.

Deviations from SQL standards: Similar to MySQL, InnoDB checks UNIQUE and foreign key constraints row by row in an SQL statement that inserts, deletes, or updates many rows. According to the SQL standard, the default behavior should be checked for delay, that is, the constraint is checked only after the entire SQL statement is processed. Before InnoDB implements the latency constraint check, some things are impossible, such as deleting a record referenced by a foreign key.

Note: currently, triggers are not activated by cascade Foreign keys.

A simple example of connecting a parent table and a child table with a single column foreign key is as follows:

CREATE TABLE parent(                    id INT NOT NULL,                    PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(                   id INT, parent_id INT,                   INDEX par_ind (parent_id),                   FOREIGN KEY (parent_id) REFERENCES parent(id)                   ON DELETE CASCADE) TYPE=INNODB;

 

 

The following is a more complex example. One product_order table has a foreign key for the other two tables. A foreign key references the double row index in a product table. Another single row index referenced in the customer table:
 

CREATE TABLE product (                      category INT NOT NULL,                      id INT NOT NULL,                      price DECIMAL,                      PRIMARY KEY(category, id)) TYPE=INNODB; CREATE TABLE customer (id INT NOT NULL,                      PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,                      product_category INT NOT NULL,                      product_id INT NOT NULL,                      customer_id INT NOT NULL,                      PRIMARY KEY(no),                      INDEX (product_category, product_id),                      FOREIGN KEY (product_category, product_id)                       REFERENCES product(category, id)                      ON UPDATE CASCADE ON DELETE RESTRICT,                      INDEX (customer_id),                      FOREIGN KEY (customer_id)                       REFERENCES customer(id)) TYPE=INNODB;

InnoDB allows you to use alter table to add a new foreign key constraint to a TABLE:

Alter table yourtablename

ADD [CONSTRAINT symbol] foreign key [id] (index_col_name ,...)

REFERENCES tbl_name (index_col_name ,...)

[On delete {RESTRICT | CASCADE | set null | no action}]

[On update {RESTRICT | CASCADE | set null | no action}]

Remember to create the required index first. You can also use alter table to add a self-referenced foreign key constraint to a TABLE.

InnoDB also supports the use of alter table to remove foreign keys:

Alter table yourtablename drop foreign key fk_symbol;

When creating a foreign key in the current year, if the foreign key clause contains a CONSTRAINT name, you can reference that name to remove the foreign key. In addition, when the foreign key is created, the fk_symbol value is internally guaranteed by InnoDB. When you want to remove a foreign key, use the show create table statement to locate the tag. Example:

 

mysql> SHOW CREATE TABLE ibtest11c\G ************************ 1. row **************************       Table: ibtest11c Create Table: CREATE TABLE `ibtest11c` (  `A` int(11) NOT NULL auto_increment,  `D` int(11) NOT NULL default '0',  `B` varchar(200) NOT NULL default '',  `C` varchar(175) default NULL,  PRIMARY KEY  (`A`,`D`,`B`),  KEY `B` (`B`,`C`),  KEY `C` (`C`),  CONSTRAINT `0_38775`  FOREIGN KEY (`A`, `D`)  REFERENCES `ibtest11a` (`A`, `D`)  ON DELETE CASCADE ON UPDATE CASCADE,  CONSTRAINT `0_38776`  FOREIGN KEY (`B`, `C`)  REFERENCES `ibtest11a` (`B`, `C`)  ON DELETE CASCADE ON UPDATE CASCADE)  ENGINE=INNODB CHARSET=latin11  row in set (0.01 sec)mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

 

The InnoDB parser allows you to enclose the table and column names in the foreign key... REFERENCES... Clause '(backticks. The InnoDB parser also takes into account the settings of lower_case_table_names system variables.

InnoDB returns the foreign key definition of a TABLE as part of the output of the show create table statement:

Show create table tbl_name;

From this version, mysqldump also generates the correct table definition to the dump file without forgetting the foreign key.

You can display the foreign key constraints for a table as follows:

Show table status from db_name LIKE 'tbl _ name ';

The foreign key constraint is listed in the output Comment column.

When a foreign key check is executed, InnoDB sets a shared row-level lock for the child or parent record it looks after. InnoDB Immediately checks the foreign key constraints and checks whether the transaction commit is delayed.

To make it easier to reload the dump file to a table with a foreign key relationship, mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0. This avoids issues related to tables that have to be reloaded in special order when the dump is reloaded. You can also manually set this variable:

Mysql> SET FOREIGN_KEY_CHECKS = 0;

Mysql> SOURCE dump_file_name;

Mysql> SET FOREIGN_KEY_CHECKS = 1;

If the dump file contains a table with an incorrect sequence of Foreign keys, the table is imported in any order. This will speed up the import operation. Setting FOREIGN_KEY_CHECKS to 0 is also useful for ignoring the foreign key restriction in the load data and alter table operations.

InnoDB does not allow you to delete a table referenced by the foreign key table constraints, unless you SET FOREIGN_KEY_CHECKS = 0. When you remove a table, the constraints defined in its creation statement are also removed.

If you recreate a removed table, it must have a definition that complies with the foreign key constraints that also reference it. It must have the correct column name and type, and as mentioned above, it must have an index on the referenced key. If these conditions are not met, MySQL Returns Error Code 1005 and points to errno 150 in the error message string.

Copyright statement: I feel very grateful if I still write well, I hope you can use your mouse and keyboard to give me a thumbs up or give me a comment! _______________________________________________________________ You are welcome to reprint. I hope to add the original address while you reprint it. Thank you for your cooperation.