Why do I need a UNIQUE constraint for a column if my application already validates the data before saving?

Question

UNIQUE is an index that makes your field unique. But is it worth using if you are already doing a PHP check before inserting new data? An additional INDEX is not the end of the world, but if you are after optimizing the query, then UNIQUE just bothers, right?

Answer 1

Why wear a seat belt if you are a good driver and you can save two seconds of your total travel time? A.

One of the most important lessons for a programmer is that he is a person and he makes mistakes. Worse, everyone who works on this code is also human.

Why is there a UNIQUE restriction? To protect the database from people making mistakes. Turning off your UNIQUE restriction says: "You don’t need to worry, Mr. DB, I will never give you data that does not correspond to my intentions."

What if something happens to your code so that your uniqueness check breaks? Your code now flushes duplicate records to the database. But if you had a UNIQUE constraint for this column, when your external code stopped working, you could get your queries.

You are a man. Accept this. Let the computer do its job and protect you from yourself.

Answer 2

UNIQUE not only guarantees the reliability of the data. The main goal is to optimize queries: if the database knows that the field is unique, it can stop searching for hits as soon as the first record is found. You cannot transfer this information to the database with well-designed queries.

Answer 3

This is an interesting question.

• Are you sure there is no way around your code?
• Are you sure that nothing else will be able to access the data next to the PHP application?
• Are you sure the rest of your application will not fail when a duplicate is inserted?
• What could the presence of duplicate entries mean, could this cause problems for future references or calculations?

These are some of the issues that eliminate the database level limitation.

As for optimization, the restriction does not lead to the fact that the process of obtaining data is noticeably slower, and in fact it can be used in the execution plan at some point, since it is associated with the index.

Thus, no, it will not interfere with optimization, and also protect your data from inconsistencies.

Answer 4

As pst mentions, at this stage in your development, you cannot start optimizing your database or the application in question.

As a rule, it’s nice to add additional additional health checks to your system. Yes, you have too much damage, but in no way notice that any user will ever notice an extra processor tick or two.

Think about it: today you are doing your check in php but not claiming uniqueness in the database. In the future, you, a colleague or some other guy who forked your project, changes the original php authentication, destroys it or completely forgets about it. At this point, you probably wish you had an added check in your database.

Answer 5

TL: other; Transaction integrity (in the database) processes the race conditions (in the application).

The Concurrency and Integrity section of these Rails documents explains why this is necessary in the sample script.

Databases with transactional integrity guarantee uniqueness through isolation , while applications actually take several separate steps (get the value, check if there are other values, and then save the value) outside of transaction isolation, which leaves them vulnerable to race conditions , especially on a scale .