MySQL FOREIGN KEY constraint

(1) Use of foreign keys:

the role of foreign keys, there are two main:
One is to let the database itself through the foreign key to ensure the integrity and consistency of the data
One is the ability to increase the readability of ER graphs
Some people think that the establishment of foreign keys will bring a lot of trouble to the development Operation database. The insert operation failed because the database was sometimes deleted by the developer because it was not detected by the foreign key. In fact, this formal foreign key is mandatory to ensure the integrity and consistency of data.

(2) Add foreign key format:  
    ALTER TABLE yourtablename 
    ADD [CONSTRAINT foreign key name] FOREIGN key [id] (index_ Col_name, ...) &NBSP
    REFERENCES tbl_name (index_col_name, ...)  
    [on DELETE {CASCADE | SET NULL | NO ACTION | restrict}] 
    [on UPDATE {CASCADE | SET NULL | NO ACTION | restrict}] 
Description:  
on delete/on update, which defines the delete,update operation. The following are the various constraint types for update,delete operations:  
cascade: 
       foreign key field values are updated, or the columns in which they are located are deleted.  
restrict: 
      restrict is also equivalent to no action, that is, no action is made. That is, deny parent table Update FOREIGN Key Association column, delete record.
set null: 
      The foreign key associated field of the parent table is update, and when delete, the outer key column of the child table is set to NULL.  
for INSERT, the value entered for the foreign key column of the child table can only be the value that is already in the parent table's Foreign Key Association column. Otherwise, an error occurred.

Foreign key definitions are subject to the following conditions: (prerequisites)
1) All tables must be of type InnoDB, they cannot be temporary tables. Because only tables of type InnoDB in MySQL support foreign keys.
2) All fields to establish foreign keys must be indexed.
3) for non-InnoDB tables, the FOREIGN key clause is ignored.
Attention:
When you create a foreign key, you cannot enclose it in quotation marks when you define a foreign key name.
such as: Constraint ' fk_1 ' or constraint "fk_1" is wrong

(3) View foreign key:
Show CREATE TABLE * *; You can view the code for the newly created table and its storage engine. You can also see the foreign key settings.
To delete a foreign key:
ALTER TABLE drop FOREIGN key ' foreign key name '.
Note: When defining a foreign key, use the constraint foreign key name foreign key .... Easy to delete the foreign key. If not defined, you can:
First ENTER: ALTER TABLE drop FOREIGN key---prompts an error. In this error message, the system default foreign key name for foreign key is displayed.--->
Use it to remove the foreign key.

(4) Example
Example one:
4.1
CREATE TABLE parent (id INT not NULL, PRIMARY KEY (ID)) Type=innodb; --type=innodb equivalent to Engine=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;
After inserting data to the parent, inserting data into child, inserting, the value of parent_id in child can only be the data in the parent, otherwise the insertion is unsuccessful;
When you delete a parent record, the corresponding record in child is also deleted;--> because: ON DELETE cascade
When updating the parent record, update;--> is not given because it is not defined and is restrict by default.
4.2
If child is as follows:
Mysql> CREATE TABLE child (ID int. NOT NULL PRIMARY key auto_increment,parent_id int, index Par_ind (parent_id),
Constraint Fk_1 foreign KEY (parent_id) references
Parent (ID) on UPDATE cascade on delete restrict) Type=innodb;
With the above:
1). When the parent record can be updated, the corresponding record in child is also updated;--> because: on UPDATE cascade
2). Cannot be a child table operation that affects the parent table. Only the parent table affects the child table.
3). Delete foreign key: ALTER TABLE child drop foreign key fk_1;
To add a foreign key:
ALTER TABLE child add constraint fk_1 foreign key (parent_id) references
Parent (ID) on update restrict on delete set null;
4.3

CREATE TABLE ' child ' (

' id ' int (one) DEFAULT NULL,

' parent_id ' int (one) DEFAULT NULL,

CONSTRAINT ' Child_ibfk_1 ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' ID ')

) Engine=innodb DEFAULT Charset=utf8

1. After inserting data to the parent, inserting data into child, inserting, the value of parent_id in child can only be the data in the parent, otherwise the insertion is unsuccessful;  

3. When you delete a record in child. The parent's record is not deleted.

4. Changes cannot be successful

(5) Multiple foreign keys exist:

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-row index referencing 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),
--Double foreign key
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES Product (category, ID)
On the UPDATE CASCADE on the DELETE RESTRICT,
--Single foreign key
INDEX (customer_id),
FOREIGN KEY (customer_id)

REFERENCES customer (ID)) Type=innodb;

(6) Description:

1. If you do not declare on Update/delete, the default is to use Restrict mode.
2. For foreign key constraints, it is best to use: on UPDATE CASCADE on DELETE RESTRICT.

3. View a table Structure statement show CREATE table child

MySQL FOREIGN KEY constraint