How to avoid duplication of content in a table without a primary key?

Question

In a virtual shop panel I am building the products have relations by color and size, where each product color has a size.
This is a part of my database to better understand the problem: http://sqlfiddle.com/#! 9/d0dee
When the user wants to associate the colors to a product in the panel it is forwarded to a page with all colors (list of checkbox) and this one he selects the colors and sends the form to php processing with the following script:
Obs.: $database is an instance of Doctrine DBAL

$checking = [];
$colors   = (array) $_POST['colors'];

$ids = [];
foreach($colors as $color) if(is_numeric($color)) $ids[]=$color;

$emptyTableQuery = $database->createQueryBuilder();
$emptyTableQuery -> delete('product_colors')
                 -> where('id_product = :idProduct AND id_color NOT IN (:ids)')
                 -> setParameters([':idProduct'=>$id,':ids'=>implode(',',$ids)]);

$emptyTable = $emptyTableQuery->execute();

foreach($colors as $color)
    $checking[] = $database->insert(
        'product_colors',
        [
            'id_product'=> $id,
            'id_color'=> $color
        ],
        ['id_product'=>\PDO::PARAM_INT,'id_color'=>\PDO::PARAM_INT]
        );

When a new product is inserted the code works perfectly, however, when the colors of the product are updated the colors already in the database are duplicated...

Before the current code ALL colors were removed from the table, or is, she was completely clean, which resulted in me having a problem major: there is a third relation with the table called 'sizes_by_product_color' where the sizes for each are stored color and the respective stocks, ie if I delete everything too this data was lost and the customer would have to register all the sizes for colors, before existing, again...

This is a part of my database to better understand the problem: http://sqlfiddle.com/#! 9/d0dee

How could I solve my problem in a practical way? there is some solution for SQL or I will have to select to check in PHP?

Answer 1

I don’t have much experience in PHP, but I do have SQL, so I’ll share my opinion:

In your code, when you save a product, all colors of a product that are not present in the selected list are removed from the database. Then, relations between product and color are added for each selected color.

This will always work the first time, since there are no colors related to a product, so there is no chance of conflict. However, when updating a product that already has color relations, you will be trying to insert repeated colors. See:

$id = 1;

-INSERÇÂO:
$colors = {"#000000","#FFFFFF"};

    -REMOÇÃO DE product_color ONDE product_id = $id E color_id NÃO ESTEJA EM $colors:
    Nada é feito. Não existem registros em product_color com product_id = $id 
    -ADIÇÃO DE UM product_color PARA CADA $colors:
    product_colors = 
    product_id | color_id
        1      | "#000000"
        1      | "#FFFFFF"

-ATUALIZAÇÃO:  
$colors = {"#000000","#112233"};    

    -REMOÇÃO DE product_color ONDE product_id = $id E color_id NÃO ESTEJA EM $colors:
    product_id | color_id
        1      | "#000000"

    -ADIÇÃO DE UM product_color PARA CADA $colors:
    product_colors = 
    product_id | color_id
        1      | "#000000" <-- Já existia e não foi deletado!
        1      | "#000000" 
        1      | "#112233"

The easiest solution I can think of is, instead of deleting only colors that were not selected in product_colors, delete them all. They will be inserted again in the following statement, anyway.

If you don’t want to do this, you probably won’t escape from selecting the existing colors in the product after delete and only insert colors that aren’t in the list.

Editing: After some research, I came to the conclusion that the general recommendation is to even check the bank to check which colors are already related to the product and not insert them. In theory, it would be possible to use the clause INSERT IGNORE Mysql if you could mark both columns as UNIQUE, but from what I read, Doctrine does not have a representation for this function (which is understandable, since it is a component to abstract DB, and not a specific implementation of Mysql). My main references were this, this and this, if you are interested in delving into the above reasons. All links are to the English OS.