PHP SQL Preventing Duplicate User names: Catching Exception vs Select Query
database, duplicates, mysql, php
Solution
In the very least, you should use option 1 - handle primary key constraint violation - when possible since the database is the last point of entry. Duplication is still possible if you check for duplicates in code (albeit highly unlikely) since there is a delay between when the initial SELECT is returned and the INSERT statement is sent back, and another user could have performed the same insert in that time window.
However, it is likely more efficient to run a select first to see if the record exists, rather than blindly letting the insert run every single time.
So I would recommend both.
Problem
For preventing duplicate user names from being entered into the database and notifying the user, is it more standard/preferred to use exception catching when inserting or select query before inserting? Exception catching: if I try to insert the user input and the user name already exists then the SQL database will throw a primary key constraint violation exception. I can catch it if that happens and do whatever. Select Query: if it returns any tuples matching the user name then I won't bother with the insert. Then I can display the error message. I suppose the main pro of using Exceptions here is there is less queries and lines (better speed?). However, I don't think this is an exceptional case since duplicates probably occur fairly often.