Ensure data specificity by limiting Unique in database (MySQL)

Tram Ho

Author: Tamesuu Source: https://qiita.com/tamesuu/items/d9475a35709ec0d49763

When are you going to limit Unique to MySQL? “When necessary when,” you might say. So when is it necessary?

In this article, author Tamesuu will talk about cases that need a unique limit

What is the unique limit?

Ensuring that the data of a column in a table is unique

URL ref: https://dev.mysql.com/doc/refman/5.6/ja/constraint-primary-key.html

When thinking about the case of Web application When you want to ensure data specificity in Web application (Rails)

On the web application (server) side, check before saving data. I will set the Unique limit on the My SQL side

So the answer to the question “When do I need to set a Unique limit in MySQL?” at the top of the post will be

  1. When testing before saving data on the Web app side (server side)
  2. Attach a unique limit to MySQL

Try to see which cases to attach:

Test environment

Test Preparation:

MySQL

On the Web application (Server) side 、 both uniq_test1 and uniq_test2 test before saving data

This time, the model will be created in the rails console

The process is roughly as follows

  1. Start transaction of MySQL
  2. Check the data for specificity
  3. Finish the transaction of MySQL (Here will create data)

The problem is: processing is done almost at the same time

Without limiting unique to MySQL (Bad pattern)

Console1

Console2

The Web app (Server) will leak data !!!

Attach a unique key limit to MySQL (Good pattern) Console1

Console2

On the web app (server) side, there is a problem, when saved in MySQL, the MySQL side will detect and give an error

Fasteners: To ensure data specificity, a unique key must be attached to MySQL. The second effect is coping in case the web app (server) is missing.

Share the news now

Source : Viblo