3

I use SQL Server 2008, and I have a table with a column of type varchar(X) which I want to have unique values.

What is the best way to achieve that? Should I use unique constraint and catch an exception, or should I pre-check before inserting a new value?

One issue, the application is used by many users so I guess that pre-checking might result in race condition, in case that two users will insert the same values.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andy
  • 1,153
  • 4
  • 13
  • 33
  • Related question - https://stackoverflow.com/questions/21889843/unique-constraint-vs-checking-before-insert – Steam Feb 19 '14 at 19:02

4 Answers4

10

Race condition is an excellent point to be aware of.

Why not do both? - pre-check so you can give good feedback to the user, but definitely have the unique constraint as your ultimate safeguard.

David Gelhar
  • 27,873
  • 3
  • 67
  • 84
  • 1
    When it comes to data validation, redundancy isn't necessarily a bad thing. Check up front for a nicer interface, check at the back end for guaranteed integrity. – araqnid Jan 17 '11 at 14:09
  • I am not aware of the race condition. Can you please tell me why one should do both when the back-end checks everything. Thanks. – Steam Feb 19 '14 at 18:49
  • @araqnid - I am unable to see how redundancy is helpful here. – Steam Feb 19 '14 at 18:50
4

Let the DB do the work for you. Create the unique constraint.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Is there any scenario where one method would be better than the other ? I have posted a related question here - https://stackoverflow.com/questions/21889843/unique-constraint-vs-checking-before-insert Please help me. Thanks. – Steam Feb 19 '14 at 19:03
3

If it's a requirement that the values be unique --- then a constraint is the only guaranteed way to achieve that. reliable so-called pre-checking will require a level of locking that will make that part of your system essentially single user.

Ralph Shillington
  • 20,718
  • 23
  • 91
  • 154
3

Use a constraint (UNIQUE or PRIMARY KEY). That way the key is enforced for every application. You could perform additional checks and handling in a store procedure if you need to - either before or after the insert.

nvogel
  • 24,981
  • 1
  • 44
  • 82