none
Conditional Insertion: Select and Insert or Insert and catch exception?

    Question

  • This is a follow on to  http://social.msdn.microsoft.com/Forums/en-US/ef89dbb5-fedb-481d-adf1-9984eb9e3880/how-to-conditionally-create-table-row

    I want to create a table with a primary key of user name.

    I could perform a SELECT statement to check to see if the current user name exists as a primary key entry in my table and, if not (because this is the first time that user has logged in), execute an insert statement.

    Or I could perform an insert statement (create a new EF object and try to save it) and see if I catch an exception.

    Which do you recommend? I anticipate that existing users logging in would be much more common than first time log-in events.

    Thanks

    siegfried

     

    siegfried heintze

    Tuesday, July 16, 2013 10:24 PM

Answers

  • What i think is that it is always good to have proper data validation at application level while inserting data into a table. In real world scenerio, there is a possibility of having same name for different person, therefore it is always good to have incremental numeric id or alphanumeric id as a primary key. Moreover, if we use SELECT statement to validate user's existance in table and then INSERT command, surely the number of execution for this process will be increased. i.e we have SELECT first for validation and then INSERT. Here the developer does not care about the existing Primary Key Constrains!

    For the other case, the number of execution at application level will be only one i.e INSERT statement. While inserting any user name in the table, the inserted values will be validated with all existing constrains and in case of any error it is catched at application level.


    Regards, RSingh

    Wednesday, July 17, 2013 3:42 AM

All replies

  • This is not good practice to use a NVARCHAR column as a primary key. It's better to have another primary key and then create a UNIQUE KEY on user name.

    And about your question, It's depend on existence probability! Please refer this thread:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/41edf525-7eb6-4fc5-a875-65c01f76632f/select-then-insert-or-insert-and-exception-handling


    http://sqldevelop.wordpress.com/



    • Edited by Saeid Hasani Tuesday, July 16, 2013 10:58 PM ....
    Tuesday, July 16, 2013 10:56 PM
  • What i think is that it is always good to have proper data validation at application level while inserting data into a table. In real world scenerio, there is a possibility of having same name for different person, therefore it is always good to have incremental numeric id or alphanumeric id as a primary key. Moreover, if we use SELECT statement to validate user's existance in table and then INSERT command, surely the number of execution for this process will be increased. i.e we have SELECT first for validation and then INSERT. Here the developer does not care about the existing Primary Key Constrains!

    For the other case, the number of execution at application level will be only one i.e INSERT statement. While inserting any user name in the table, the inserted values will be validated with all existing constrains and in case of any error it is catched at application level.


    Regards, RSingh

    Wednesday, July 17, 2013 3:42 AM