MySQL FOREIGN key explanation

Recently in learning Hibernate, always by the foreign key to make a brain, and, their own MySQL foreign key learning is not very solid, so in order to better master hibernate, MySQL, so, in the online search some learning MySQL to build foreign keys and add foreign key blog, And a profound study of a bit, feeling cool, mom no longer have to worry about my foreign key!

Here is a summary of the knowledge points for foreign keys!

InnoDB also holds foreign key constraints. The syntax for FOREIGN KEY constraint definitions in InnoDB looks like this:

[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}]

Foreign key definitions are subject to the following conditions:

· All tables must be of type InnoDB , they cannot be temporary tables .

· In the reference table, there must be an index in which the foreign key columns are listed in the same order as the first column. If such an index does not exist, it must be created automatically in the reference table.

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

· Index prefixes for foreign key columns are not supported. One consequence of this is that the blob and text columns are not included in a foreign key, because the indexes on 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 child table and does not match the candidate key value in the parent table. A parent table has a child table of matching rows, and InnoDB is acting on any update or delete operation that attempts to update or delete the candidate key values in the parent table, depending on the on update and on with the foreign KEY clause The referential action specified by the DETETE clause. When a user attempts to delete or update a row from a parent table, and there is one or more matching rows in the child table, InnoDB has five choices depending on the action to be taken:

· CASCADE: Deletes or updates from the parent table and automatically deletes or updates the matching rows in the child table. Both on DELETE Cascade and on UPDATE Cascade are available. Between two tables, you should not define a number of on UPDATE cascade clauses that take actions on the same column in the parent table or child table.

· set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If the foreign key column does not specify a NOT NULL qualifier, this is the only legal one. On DELETE set null and on UPDATE SET NULL clauses are supported.

· noaction: in the ANSI SQL-92 standard, no action means this does not take action, that is, if there is an associated foreign key value in the referenced table, the attempt to delete or update the primary key value is not allowed (Gruber, mastering SQL, 2000:181). InnoDB rejects the delete or update operation on the parent table.

· RESTRICT: denies deletion or update of the parent table. NO action is the same as restrict, deleting the on delete or on UPDATE clause. (Some database systems have deferred checks, and no action is a deferred 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 resolver, but InnoDB rejects the table definition that contains the on DELETE set default or on UPDATE set default clause.

InnoDB supports the same selection when the candidate keys in the parent table are updated. Select Cascade, and the foreign key column in the child table is set to the new value of the candidate key in the parent table. In the same way, if the column that is updated in the child table references a foreign key in another table, the update is cascaded.

Note that InnoDB supports foreign keys in a table reference, in which case the child table actually means that this is an attached record within the table.

InnoDB requires an index of foreign keys and referenced keys so that foreign key checks can be done quickly and no table scan is required. The index of the foreign key is created automatically. This is relative to some of the older versions in which the index must be explicitly created, otherwise the creation of the foreign KEY constraint will fail.

Within InnoDB, the corresponding columns in the foreign key and referenced joins must have similar internal data types so that they can be compared without the need for type conversions. The size and symbol of an 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 do not declare the column as not NULL in the child table.

If MySQL reports an error number 1005 from the CREATE TABLE statement, and the error message string points to errno 150, this means that the table creation failed because a FOREIGN key constraint was improperly formed. Similarly, if ALTER TABLE fails, and it points to errno 150, it means that the foreign key definition is incorrectly formed for the changed table. You can use Show INNODB status to display a detailed explanation of the most recent INNODB foreign key error on the server.

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

Deviation from the SQL standard: If there are several rows within the parent table, with the same referenced key value, and then InnoDB action in the foreign key check, as if other parent rows with the same key value do not exist. For example, if you have defined a constraint of type restrict and have a child row with several parent rows, InnoDB does not allow any deletions to those parent rows.

A record in the index corresponding to the FOREIGN key constraint, InnoDB the cascade operation through the depth-first selection method.

divergence from the SQL standard: if on UPDATE cascade or on update SET null recursively updates the same table, the table was previously updated during the Cascade process, and it acts like restrict. This means that you cannot use the self-referencing on UPDATE cascade or the on Update SET null operation. This will prevent an infinite loop caused by cascading updates. On the other hand, a self-referencing on delete SET null is possible, just like a self-referencing on DELETE cascade. Cascading operations can not be nested more than 15 layers deep.

divergence from the SQL standard : Similar to general MySQL, within an SQL statement that inserts, deletes, or updates many rows, InnoDB checks the unique and foreign key constraints line by row. According to the SQL standard, the default behavior should be deferred check, that is, the constraint is checked only after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are not possible, such as deleting a record that is referenced to itself by a foreign key.

Note: Currently, the trigger is not activated by the action of the Cascade foreign key.

A simple example of a parent table and a child table via 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 pare NT (ID) on                   DELETE CASCADE) Type=innodb;

The following is a more complex example where a product_order table has foreign keys for the other two tables. A foreign key references a double-column index in a product table. Another single-line index referencing in the Customer table:

CREATE TABLE Product (                      category INT NOT NULL,                      ID int. NOT NULL, price                      DECIMAL,                      PRIMARY KEY (category, id)) T Ype=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                      the UPDATE CASCADE on DELETE RESTRICT,                      INDEX (customer_id),                      FOREIGN KEY (customer_id)                       REFERENCES customer (ID)) Type=innodb;

InnoDB allows you to add a new foreign KEY constraint to a table with ALTER 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 desired index first. You can also use ALTER TABLE to add a self-referencing foreign KEY constraint to a table.

InnoDB also supports the use of ALTER TABLE to remove the exception key:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

When you create a foreign key in the current year, if the foreign KEY clause includes a constraint name, you can refer to that name to move the excepted key. In addition, when the foreign key is created, the Fk_symbol value is InnoDB internally guaranteed. To find the tag when you want to remove a foreign key, use the show CREATE table statement. Examples are as follows:

mysql> SHOW CREATE TABLE ibtest11c\g ************************ 1. Row **************************       table:ibtest11c Create table:create Table ' ibtest11c ' (  ' A ' int (one) not NULL auto _increment,  ' D ' int (one) not null default ' 0 ',  ' B ' varchar ($) NOT null default ' ',  ' C ' varchar (175) Default N ULL,  PRIMARY key  (' A ', ' D ', ' B '),  key ' B ' (' B ', ' C '),  key ' C ' (' C '),  CONSTRAINT ' 0_38775 '  FOREIGN KEY (' A ', ' d ')  REFERENCES ' ibtest11a ' (' A ', ' d ') on the  DELETE CASCADE on UPDATE CASCADE,  CONSTRAINT ' 0 _38776 '  FOREIGN KEY (' B ', ' C ')  REFERENCES ' ibtest11a ' (' B ', ' C ') on the  DELETE CASCADE on UPDATE CASCADE) 
   
    engine=innodb charset=latin11  Row in Set (0.01 sec) mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
   

InnoDB parsing program allows you to foreign KEY ... REFERENCES ... The table and column names are enclosed in clauses with ' (backticks). The InnoDB parser also takes into account the settings of the Lower_case_table_names system variable.

InnoDB returns a foreign key definition for a table as part of the output of the show CREATE table statement:

SHOW CREATE TABLE tbl_name;

From this release, mysqldump also generates the correct definition of the table to the dump file, and does not forget the foreign key.

You can display foreign key constraints on a table as follows:

SHOW TABLE STATUS from db_name like ' tbl_name ';

Foreign KEY constraints are listed in the Comment column of the output.

When a foreign key check is performed, InnoDB sets a shared row-level lock on the child or parent record that it is looking after. InnoDB immediately checks for foreign key constraints and checks for no transaction commit delay.

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

mysql> SET foreign_key_checks = 0;

Mysql> SOURCE Dump_file_name;

mysql> SET foreign_key_checks = 1;

If the dump file contains tables with foreign keys that are not in the correct order, this imports the table in any order. This also speeds up the import operation. Setting Foreign_key_checks to 0 is also useful for ignoring foreign key restrictions in the load data and ALTER TABLE operations.

InnoDB does not allow you to delete a table that is referenced by the FOREIGN KEY table constraint unless you do set foreign_key_checks=0. When you remove a table, the constraints defined in its creation statement are also removed.

If you recreate a table that has been removed, it must have a definition that follows the FOREIGN KEY constraint that also references it. It must have the correct column name and type, and as mentioned earlier, it must have an index on the referenced key. If these are not met, MySQL returns error number 1005 and points to errno 150 in the error message string.

Copyright notice: I feel like I'm doing a good job. I hope you can move your mouse and keyboard for me to order a praise or give me a comment, under the Grateful!_____________________________________________________ __ Welcome reprint, in the hope that you reprint at the same time, add the original address, thank you with

MySQL FOREIGN key explanation