MySQL foreign key settings

(1) usage of Foreign keys:

The foreign key has two main functions:
One is to allow the database to ensure data integrity and consistency through foreign keys.
One is to increase the readability of the erimage.
Some people think that the establishment of foreign keys will cause a lot of trouble in database operations during development. because the database sometimes fails to pass the foreign key detection, the developer deletes the database and the insertion operation fails. they think this is very troublesome.
In fact, this formal foreign key forces you to ensure data integrity and consistency. This is a good thing.
For example:
There is a basic data table used to record all information about the product. The product ID is saved in other tables. You need to connect to the table to query the product name. The item Id field exists in the item table of document 1, and the item Id field exists in the item table of document 2. If you do not use a foreign key, when both products with commodity id = 3 are used in document 1 and 2, if you delete the corresponding records with ID = 3 in the commodity table, when you view the document, the product name cannot be found.
When there are few tables, some people think that data integrity and consistency can be ensured by writing scripts during program implementation. That is, when deleting a product, check whether the product with the product ID 3 is used in document 1 and 2. However, after you write the script, the system adds a document 3 and stores the item ID to find a field. If you do not need a foreign key, you still cannot find the product name. You cannot modify the script to check whether the product is used every time you add a document that uses the item Id field. At the same time, the introduction of foreign keys will degrade the speed and performance.

(2) Format of adding a 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}]
Note:
On delete/on update, used to define Delete and update operations. The following are various constraints for update and delete operations:
Cascade:
The foreign key field values in the foreign key table are updated, or the column where the foreign key field is located is deleted.
Restrict:
Restrict is also equivalent to no action, that is, it does not perform any operation. That is, it rejects the foreign key Association column of the parent table update and deletes the record.
Set NULL:
When the field associated with the parent foreign key is updated and deleted, the foreign key column of the child table is set to null.
For insert, the value entered in the foreign key column of the child table can only be an existing value in the foreign key Association column of the parent table. Otherwise, an error occurs.

The foreign key definition follows the following conditions: (Prerequisites)
1)
All tables must be of the InnoDB type and cannot be temporary tables, because only InnoDB tables in MySQL support foreign keys.
2)
All fields to create a foreign key must be indexed.
3)
For non-InnoDB tables, the foreign key clause is ignored.
Note:
When creating a foreign key, when defining a foreign key name, no quotation marks are allowed.
For example, constraint 'fk _ 1' or constraint "fk_1" is incorrect.

(3) view foreign keys:
Show create table ***; you can view the code of the newly created table and its storage engine. You can also see the settings of Foreign keys.
Delete foreign key:
Alter table drop foreign key 'foreign key name '.
Note:
Only when defining foreign keys, use the constraint foreign key name foreign key... to delete Foreign keys.
If not defined, you can:
Enter alter table drop foreign key --> and an error is displayed. The system default foreign key name of foreign key is displayed in the error message. --->
Use it to delete the foreign key.

(4) Example

Instance 1:
4.1
Create Table parent (ID int not null,
Primary Key (ID)
) Type = InnoDB; -- type = InnoDB is 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 data is inserted into the parent, data is inserted into the child. During the insertion, the parent_id value in the child can only be data in the parent; otherwise, the insertion is unsuccessful;
When you delete a parent record, the corresponding record in child will also be deleted; --> because: On Delete Cascade
When updating a parent record, do not update it. --> because it is not defined, restrict is used 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;
Above:
1 ).
When the parent record can be updated, the corresponding record in child will also be updated; --> because: On update Cascade
2 ).
It cannot be a sub-table operation, which affects the parent table. It can only be a parent table that affects the sub-table.
3 ).
Delete foreign key:
Alter table child drop foreign key fk_1;
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 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 ),
-- Double foreign key
Index (product_category, product_id ),
Foreign key (product_category, product_id)
References product (category, ID)
On update cascade on Delete restrict,
-- Single foreign key
Index (customer_id ),
Foreign key (customer_id)

References customer (ID) type = InnoDB;

(6) Description:

1. If on update/delete is not declared, the restrict method is used by default.
2. For foreign key constraints, it is best to use: On update cascade on Delete restrict.

Reprinted to the network