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

Question

UNIQUE is an index which makes your field, well, unique. But is it worth using it if you're already doing your validation in PHP prior to inserting new data? An extra INDEX isn't the end of the world but if you're after query optimization then UNIQUE just gets in the way, right?

Answer 1

Why wear a seat belt if you're a good driver and you can save two seconds of your total trip time?

One of the most important lessons for a programmer to learn is that he is human and he makes mistakes. Worse, everyone else working on this code is human, too.

Why does the UNIQUE constraint exist? To protect the database from humans making mistakes. Turning off your UNIQUE constraint says "You do not need to worry, Mr. Database, I will never give you data that doesn't match my intent."

What if something happens to your code such that your validation for uniqueness breaks? Now your code dumps duplicate records into the database. But if you had a UNIQUE constraint on that column, when your front-end code stopped working, you'd get your queries blowing up.

You're human. Accept it. Let the computer do its job and help protect you from yourself.