Is there any disadvantages of unique column in MYSQL

Question

i'd like to ask a question regarding Unique columns in MySQL. Would like to ask experts on which is a better way to approach this problem, advantages or disadvantages if there is any.

• Set a varchar column as unique
• Do a SQL INSERT IGNORE
• If affected rows > 0 proceed with running the code

versus

• Leave a varchar column as not-unique
• Do a search query to look for identical value
• If there is no rows returned in query, Do a SQL INSERT
• proceed with running the code

Answer 1

Neither of the 2 approaches is good.

You don't do INSERT IGNORE nor do you search. The searching part is also unreliable, because it fails at concurrency and compromises the integrity. Imagine this scenario: you and I try to insert the same info into the database. We connect at the same time. Code in question determines that there's no such record in the database, for both of us. We both insert the same data. Now your column isn't unique, therefore we'll end up with 2 records that are the same - your integrity now fails.

What you do is set the column to unique, insert and catch the exception in the language of your choice. MySQL will fail in case of duplicate record, and any proper db driver for MySQL will interpret this as an exception. Since you haven't mentioned what the language is, it's difficult to move forward with examples.