How to check for duplicate usernames?

[Jeff Massung]

On Thu, Aug 4, 2011 at 10:32 AM, Bob Sneidar <bobs at twft.com> wrote:

> Ick! Do stuff that causes SQL to throw errors. I suppose that is a way to
> do it, but a simple select will do it cleanly. Keeps the SQL error logs
> clean too. If multiuser, lock the SQL database first, then check, insert if
> query returns 0 records, unlock the SQL database.
>
>
All are perfectly valid solutions. Preference for one method over another
should be based on reasons (if present) and personal preference otherwise.

Using a UNIQUE field can slow down INSERT operations are very large tables
if you are performing mass inserts. A user table typically doesn't have this
problem. Marking a field as UNIQUE prevents errors when working with the
database in an interactive mode. Perhaps the OP does this (probably not,
though, given the nature of the question). Doing a SELECT to lookup and then
a follow-up INSERT is solving the same problem twice: search a B-tree for a
given value, and search it again when determining where to insert the new
key. Marking as UNIQUE allows you to do this work only once, which might be
a performance win; profiling is needed.

Keep in mind, it's not causing "SQL to throw errors" as in "let's create
egregious code and break things." Referential integrity, transactions,
uniqueness, foreign keys, these things all exist for a reason: to prevent
bad things from happening now or down the road when the rules you put in
place aren't quite as fresh in memory. Using those tools when appropriate is
*highly* recommended. This may be one of those instances. Perhaps not. But a
quick, knee-jerk reaction like "ick" is hardly a deliberate, thought-through
reason to go down one path over another.

I don't mean to cause a flame war, incite, or imply anything negative. I
simply want the OP to not walk away from this thread thinking that UNIQUE is
bad in some way, or that tossing actions at SQL that "throw errors" is a bad
thing either. Look at the problem, the pros/cons of various solutions, and
pick your poison wisely.

Jeff M.