Prevent Duplicate Entries On A Pivot / Linking Table in MySQL (with PHP / PDO)

Emmy 0
Viewed 100

I currently have a many-to-many linking/pivot table called boards_images in MySQL that holds two columns, namely a board_id column and an image_id column, which are both foreign keys from a boards table and an images table respectively.

When a user adds an image to a board MySQL currently just adds the appropriate values with a simple INSERT statement via PHP / PDO:

// $boardId and $imageId are values taken from input elements on the submitting form.

$sql = "INSERT INTO boards_images (board_id, image_id) 
VALUES (:board_id, :image_id)";
";

$stmt = $connection->prepare($sql);

$stmt->execute([
    ':board_id' => $boardId
    ':image_id' => $imageId
]);

However, this INSERT statement clearly doesn't prevent images that are already on a board from being added multiple times again to the same board.

What is the best way to prevent this? Will I need to make an initial SELECT statement with the board_id value? Such as:

SELECT * FROM boards_images where board_id = :board_id

...and then run a check against this? And if so how do I check for the duplicate image_id value? I perhaps naively thought changing the INSERT to INSERT IGNORE would be what I needed, but it seems not.

many-to-many duplicates mysql
1 Answers

You need an UNIQUE composite index on the PIVOT table. This index should include both board_id and image_id fields.

ALTER TABLE `pivot` ADD UNIQUE `my_unique_index` ( `board_id`, `image_id`) ;

Then in your INSERT SQL just do this to silently deal with duplicates:

$sql = "INSERT INTO boards_images (board_id, image_id) VALUES (:board_id, :image_id) ON DUPLICATE board_id = :board_id ;";

If you try to insert a duplicates, the UNIQUE INDEX will prevent to insert the duplicate record and the ON DUPLICATES clause will silently discard the INSERT without generating an error.

Alaindeseine0
Related