Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL: Use unique column or SELECT before INSERT

Options
  • 17-03-2010 6:55pm
    #1
    daymobrew
    Registered Users Posts: 6,509 ✭✭✭


    I am adding rows to my database table. If a username is already in the table I don't want to add it again.

    Should I make the 'username' field UNIQUE and let the db reject duplicate usernames or should I do a SELECT search before trying an INSERT?
    (Will the mysql_errno error number inform me of a clash?)


Comments

  • Options
    #2
    CuLT
    Subscribers Posts: 9,716 ✭✭✭CuLT


    UNIQUE is probably the best for consistency; SELECTs can (and do) fail.

    You should get a warning in the return.


  • Options
    #3
    AndrewMc
    Registered Users Posts: 354 ✭✭AndrewMc


    It's safer to use a UNIQUE column and be prepared for the INSERT to fail, since in a multi-user situation there's a chance that two users might try to create the same username at the same time; the two selects could occur before the two inserts, and you end up with the name added twice.

    If you have a constraint in your data like this, it's always a good idea to add it to the database schema anyway. Most of the time it's better to have your program fail than to have your data inconsistent.


  • Options
    #4
    Sparks
    Registered Users Posts: 40,038 ✭✭✭✭Sparks


    UNIQUE will be faster and more reliable, in general.
    Look at it this way - if you can offload the check to a standard part of the database, you're handing it over to code that has a lot more manhours put into it than your client-side code will, at least in most cases. There's no point in reinventing the wheel, especially not when you're standing in the front yard of Acme Wheels, Inc.

    Similar arguments go for stored procedures -v- client-side code. About the only downside with offloading is that it can be a bit of a pain to do proper version control on database schemas and routines.


Advertisement