How to update a junction table?

Frameworks are well and good, but sometimes they shield us from simple concepts that we, as developers, should know. Let me tell you a story about this theme, and what happened.

I was handed a really simple task: I had to create two entities (Store and Brand). A Store could have had many brands attached, so it was a simple many-to-many relationship, with a junction table. Something like:

create table stores(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE = INNODB;

create table brands(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE = INNODB;

create table store_brand (
  store_id INT ,
  brand_id INT,
  PRIMARY KEY (store_id, brand_id),
  FOREIGN KEY store_fk (store_id) REFERENCES stores(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY brand_fk (brand_id) REFERENCES brands(id) ON UPDATE CASCADE ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8, ENGINE = INNODB;

INSERT INTO brands(name) VALUES
('Scitenoa'),
('Drirathiel'),
('Trelod'),
('Jiofrax'),
('Chacaka'),

INSERT INTO stores(name) VALUES
('Castrealm');

INSERT INTO store_brand VALUES (1,1), (1,2), (1,3);

The brands were fixed, so I had to create a simple CRUD interface only for the stores. Insertion and deletion were a breeze to code. When I came to write the code for the Update part, I stopped: “How should I write it?”

The problem

When you update a many-to-many relation you could not call a simple UPDATE statement on the junction table, you have to:

1. Insert all the newly associated entities,
2. Remove all the entities that are no more part of the relation.

Solution 1: Delete all the old tuples and insert

The first solution was very trivial: remove all the rows from the junction table related to a given store, and insert the new rows.

DELETE FROM store_brand WHERE store_id=1;
INSERT INTO store_brand VALUES (1,1), (1,4), (1,5);

This would have done well, but the fact that I was deleting and inserting a set of rows that could potentially differ only for one item was disturbing me. So I went on to search a new solution.

Solution 2: insert only new tuples and drop the ones that were not inserted

I was looking for a way to insert only new rows: the ignore clause from MySQL was good for this. Then I had to drop all the rows that were not in the set of those just added. In code:

INSERT IGNORE INTO store_brand VALUES (1,1), (1,2), (1,3);
DELETE FROM store_brand WHERE brand_id NOT IN (1,2,3) AND store_id=1;

Conclusion

So that’s was it! Sometimes is good to be looking at these low-level details, that often are hidden behind super-optimized libraries.

Cover image by Scott Robinson