I have two tables: product_content and product_images . In the first one I store all the textual information about the product, in the other I store the address for the images.

Contracture tables:

product_content:

 id - primary key
 url - varchar
 title - varchar
 content - text
 meta_title - text
 meta_description - text
 meta_keywords - text
 deleted - int (0 | 1)

product_images

 id - primary_key
 product_id - int
 full_size - text
 mini_size - text
 deleted -int (0 | 1)

When I add a product, I add two queries to the database:

 `$ query_content =" INSERT INTO product_content 
               (url, title, content, meta_title, meta_description, meta_keywords) 
        VALUES (: url,: title,: content,: meta_title,: meta_descritpion,: meta_keywords) ";`

Then I get the last id from the query $query_content and pass to the next request to insert images. The request is executed in a loop.

 `$ query_image =" INSERT INTO product_images
               (product_id, full_size, mini_size) 
        VALUES (: product_id,: full_size,: mini_size) ";`

Question: If I make the product_images column product_id - foreign key, how will this help me? Will I need to get the id of the product that I inserted into the product_content table? I just don’t understand why there is such an index as Foreign Key. Could you explain by my example. The documentation looked, did not understand anything. Thank.

    1 answer 1

    A foreign key is needed to control data integrity (in MySQL, it only works for the InnoDB engine). It is needed so that in the product_images table there is no product_id that is not in product_content

    • That is, in any case, I have to get the last id of the record from the product_content table when inserting ?. I use the engine only InnoDB, because all my requests go through transactions. Thanks for the answer. - Rosnowsky
    • Yes. But if you have a foreign key, the DBMS will control the data more tightly and you will be able to detect the error at an earlier stage - Anton Shchyrov