Unique constraint vs pre checking
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 cons开发者_JAVA技巧traint 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
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.
Let the DB do the work for you. Create the unique constraint.
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.
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.
精彩评论