Hello. Please tell me how to implement this. There are 2 tables products (id, name, description) and images (id, id_product, name)

And I want to make a link, that if a product is deleted, then all images are deleted with this product, but at the same time, so that you can create a product WITHOUT images. I tried it like this

ALTER TABLE tb_product ADD CONSTRAINT tb_product_tb_image_id_product_fk FOREIGN KEY (id) REFERENCES tb_image (id_product);

It seems all is well, but I can not create a product without images.

  • 2
    You have a link in the wrong direction, the image must refer to the product (ie, the konstraint is added to the image table) and it needs to be done ON DELETE CASCADE to automatically delete the image - Mike
  • one
    You are mistaken with the direction of the constraint. In general, it is not clear how you manage to create FK on a clearly NOT-unique index ... - Akina

1 answer 1

This is because they are confused in the direction of communication. The constraint is formed in the dependent table. (and as I understand the picture is a dependent object from the product)

 ALTER TABLE tb_images ADD CONSTRAINT tb_image_tb_product_id_product_fk FOREIGN KEY (product_id) REFERENCES tb_product (id) ON DELETE CASCADE;

In theory, such a cascade deletion will result in 1. creating an index on the product_id field in the image table, which will speed up joins 2. inability to insert an image without a product 3. to remove related images when deleting a product