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

Question

Background

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

I implement an auto save function that saves form data 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; 

Keep in mind that only drafts are stored in this table, not actual reviews . If we are editing a review, review_id will point to that review. If we create a new review, this field will be NULL .

---

What works

This is my request to insert 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 is great for inserting new draft reviews. Indexes forbid the insertion of a new row in which product_id and user_id combinations already exist.

---

What does not work

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

UNIQUE index allows multiple NULL values ​​for columns that may contain NULL

As long as there are questions and answers about the above quote, I'm not necessarily interested in getting a zero value as part of a unique index, but rather finding a workaround.

I think I could first make a selection request to check if the above combination exists, and if the main request is not continued. But I would like to get all this in one request, if possible. Ideas?

Thanks.

Answer 1

Instead of review_autosave_data you can create two tables, such as review_insert_drafts and review_update_drafts (one for new reviews and one for viewing 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`) ); 

(I don’t know what the name column is suitable for.)

In your application, you should check to see if the user is writing a new review or 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"); 

To view the updates that 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"); 

Benefits: 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 two tables (for example, show all the drafts for the user).

Answer 2

Instead of using NULL use 0 so that there is no overview.

Then change DEFAULT NULL to NOT NULL .

Meanwhile, you can also get rid of id and just have PRIMARY KEY(product_id, user_id, review_id)

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

Why do you have a “review” like a BLOB when it is supposedly “TEXT”?

Answer 3

Just make a second table that will contain the published reviews. And as soon as users view messages, move the data there.

This will fix your problem and make everything clearer. You will have one place for drafts that should not be placed anywhere, and one table on which the review will be published, which you want to display in different places. Your current table name also assumes that it contains only autosave data and non-published data.

Answer 4

 INSERT INTO review_autosave_data (review_id, product_id, user_id, review) SELECT 25, 50, 1, "lorem ipsum" WHERE NOT EXISTS (SELECT review_id FROM review_autosave_data WHERE product_id=50 AND user_id = 1 AND review_id IS NULL) ON DUPLICATE KEY UPDATE review = "lorem ipsum"; 

Answer 5

Have you tried to insert a new line without review_id?

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

Answer 6

You can try to make product_id and user_id your primary key:

 PRIMARY KEY(`product_id`, `user_id`) 

and add review_id as your UNIQUE KEY

Answer 7

Why not completely move the problem from the database?

Presumably, when your application sends an autosave request, it will receive some confirmation that this happened correctly? You can send the “ID” of the insert so that the application can then save to this record explicitly - otherwise, autosave will be different at first, and the rest and subsequent autosaves will just update the specific autosave record.

This completely fixes the problems at your DB level, I think ... you will have two queries, although one to insert for a new draft, and then the other to update a specific draft, without having to use the necessary buttons.