Mysql foreign Key settings

MySQL foreign key set up detailed

(1) Use of foreign keys:

The role of the foreign key, 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 of operational databases. The insert operation failed because the database was sometimes deleted by the developer because it was not detected by the foreign key. They think it's a hassle.
In fact, this formal foreign key is forcing you to ensure the integrity and consistency of the data. This is good.
        For example:
        has an underlying data table to record all information about the product. The other tables hold the item ID. Queries require a table to query the name of the item. There is a commodity ID field in the Product table for document 1, and the Commodity ID field in document 2. If the foreign key is not used, when the document is used for goods id=3 commodity, if you delete the id=3 in the product list of the corresponding records, and then check the document when the name of the product will not be found.
      When the table is very small, some people think that it is possible to write scripts to ensure the integrity and consistency of the data when the program is implemented. That is, when deleting the operation of the product to detect whether the document has been used in the Product ID 3. But when you finish writing the script and the system has added a document 3, he also saves the product ID to find a field. If you do not use the foreign key, you will still appear to find the product name of the case. You can't always go back and modify the script that detects if a product is being used, every time you add a document that uses the item ID field, and the introduction of a foreign key can slow down the speed and performance.

(2) to add the format of the foreign key:
ALTER TABLE yourtablename
       add [CONSTRAINT foreign Key name] FOREIGN KEY [id] (index_col_name, ...)
       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 the update,delete operation:
CASCADE:
Foreign key table foreign key field value will be updated, or the column will be 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:
When the Foreign Key association field of the parent face is update, delete, the foreign 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 a foreign key 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 '.
Attention:
Only when defining foreign keys, 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 a parent table. Only the parent table affects the child table.
3).
To delete a 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;

(5) Multiple foreign keys exist:

The 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)) 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)

           c17> c27> 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.

Mysql foreign Key settings