Check if values exist before INSERT INTO ... ON DUPLICATE KEY UPDATE

Question

Background

I have an an application where users can submit reviews for a product. Users can also edit previously submitted reviews or submit another one for the same product.

I am implementing an "auto-save" feature that saves the form data once every X seconds. If the page is accidentally closed, the user can restore this "draft".

This is a simplified version of my table:

CREATE TABLE `review_autosave_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `review_id` int(11) unsigned DEFAULT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`product_id`, `user_id`),
  KEY `fk_review_autosave_data_review_id (`review_id`),
  KEY `fk_review_autosave_data_product_id (`product_id`),
  KEY `fk_review_autosave_data_user_id (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Bear in mind that this table only stores the drafts - not the actual reviews. If we're editing a review review_id will point to that review. If we're in the process of creating a new review, this field will be NULL.

---

What's working

This is my query for inserting a new draft:

INSERT INTO review_autosave_data (review_id, product_id, user_id, review)
VALUES (25, 50, 1, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

This works fine for inserting new review-drafts. The indexes prevent inserting a new row where the combination of product_id and user_id already exist.

---

What's not working

My problem is for inserting drafts for existing reviews, where review_id needs to point to an existing review because, ideally, the index here needs to be the combination of product_id, user_id and review_id. Unfortunately in my case, the following applies:

a UNIQUE index permits multiple NULL values for columns that can contain NULL

While there are questions and answers about the above quote, I'm not necessarily interested in achieving getting a null value be part of a unique index - but rather to find a workaround.

I guess I could first make a select query to check if the above combination exists, and if not proceed with the main query. But I'd like to get all that into one query if possible. Ideas?

Thanks.

Answer 1

Instead of review_autosave_data you can create two tabels like review_insert_drafts and review_update_drafts (one for new reviews and one for review updates).

CREATE TABLE `review_insert_drafts` (
  `product_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`product_id`, `user_id`),
  CONSTRAINT FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `review_update_drafts` (
  `review_id` int(11) unsigned NOT NULL,
  `review` blob,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`review_id`),
  CONSTRAINT FOREIGN KEY (`review_id`) REFERENCES `reviews` (`id`)
);

(Not sure what the name column is good for.)

In your application you have to check if the user is writing a new review or is updating an existing one.

For new reviews you run:

INSERT INTO review_insert_drafts (product_id, user_id, review)
VALUES (50, 1, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

or

REPLACE INTO review_insert_drafts (product_id, user_id, review)
VALUES (50, 1, "lorem ipsum");

For review updates you run:

INSERT INTO review_update_drafts (review_id, review)
VALUES (25, "lorem ipsum")
ON DUPLICATE KEY
UPDATE review = "lorem ipsum";

or

REPLACE INTO review_update_drafts (review_id, review)
VALUES (25, "lorem ipsum");

Advantages: You have a clear design with clear unique keys and foreign keys.

Disadvantages: You have two tables containing similar data. So you have two different insert statements. And you will need a UNION statement if you want to combine the two tables (e.g. show all drafts for a user).

Answer 2

Instead of using NULL, use 0 to mean no review yet.

Then change DEFAULT NULL to NOT NULL.

Meanwhile, you may as well get rid of id and simply have PRIMARY KEY(product_id, user_id, review_id)

Also get rid of any index that is a prefix of the PRIMARY KEY.

Why do you have the "review" as BLOB, when it is presumably "TEXT"?