0

I have a table 'users' that has, among others, the columns 'email' and 'username'. I want to make sure that when an account is created, no account(row) already exists with the same username or email. Currently, the program simply checks if the number of rows where the email or username is the same, and if the count is zero, insert a row containing the email and username.

However, this is two separate queries, and since this is a multi-threaded web app, it introduces the possibility of a race condition, resulting in two accounts with the same username, causing problems when either tries to login.

So, my question is: Is there a way to, in a single SQL query, insert a row only if there aren't any rows like it(i.e. with the same username)?

For those wondering, I am using the Sequel gem in ruby.

the Tin Man
  • 150,910
  • 39
  • 198
  • 279
Shelvacu
  • 3,704
  • 20
  • 41
  • It sounds like you are looking for INSERT IGNORE. See http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – Matt Bryant Jul 09 '13 at 04:05
  • I knew this question had to have already been asked, I just didn't know what to search for. Thank you. – Shelvacu Jul 09 '13 at 06:40

1 Answers1

2

One very good method to solve this issue is to define a unique constraint on the columns you mentioned. Then, if you attempt to insert duplicates the insertion will fail with a trappable error.

Using this method also provides you with a centralized, consistently applied mechanism to ensure data integrity with the database itself, rather than any programs that access the database.

STLDev
  • 5,459
  • 21
  • 33