MySQL FOREIGN key constraint learning notes

In a project, when we build a data form, two representations are associated, and the data in one table is completely dependent on the data of another table,

FOREIGN KEY constraint syntax

[CONSTRAINT [Symbol]] FOREIGN KEY
[Index_name] (Index_col_name, ...)
REFERENCES tbl_name (Index_col_name,...)
[on DELETE Reference_option]
[on UPDATE Reference_option]

Reference_option:
RESTRICT | CASCADE | SET NULL | The use of the NO ACTION foreign key requires that the following conditions be met:

1. Two tables must all be innodb tables, and they do not have temporary tables.

2. The corresponding column that establishes the foreign key relationship must have a similar INNODB internal data type.

3. The corresponding column establishing a foreign key relationship must have an index established.

4. If the constraint symbol is explicitly given, then symbol must be unique in the database. If not explicitly given, InnoDB will be created automatically.

For example: Table 1:rou_products, table 2:rou_pro_images;

Ru_products (ID,....) is a commodity information sheet, ru_pro_images (id,pro_id,...) is a product album table, Ru_products and ru_pro_images are 1:n relations;

The requirement now is that when you delete a product, all of the album data for that item is deleted, in two ways:
Way one: Implement in code, delete deletes from ru_products ... Select the ID of the data before, and then, based on this ID, delete the ru_images table
pro_id = ID of the data;
Mode two: In the CREATE table ru_images, the establishment of foreign key constraints, so that when the table ru_products deleted, the database will automatically help us to delete the ru_images and ru_products table deleted the corresponding data; Not only save code, reduce workload, but also ensure data consistency

It is recommended that foreign key constraints be specific in the following ways:

1, establish the index of the pro_id field: ALTER TABLE

Ru_images
Add index (pro_id);//If pro_id has already added an index, ignore this step;

2, the proposed FOREIGN KEY constraint: ALTER TABLE

Ru_images
ADD CONSTRAINT fk_pro_img froeign KEY (pro_id) REFERENCES
Ru_products
(ID) on DELETE CASCADE;
Where the on delete CASCADE indicates that the cascade deletes the ru_images when the ru_products is deleted; There are other options available here, of course:
[on DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[on UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

The CASCADE represents a cascade operation; SET null means null; No action means no action; RESTRICT indicates that when a product in Ru_products has an album data, it is not allowed to delete the product;

In which, when you add a foreign key constraint, there are a few points to note:

One, the fields that add foreign keys must first be indexed;
Second, when there is data in the datasheet, it may cause the addition of a foreign key constraint to fail