Unique column, checking for existing record, best approach

Question

Suppose I have a table with PK column, some other columns, and one which must be unique.

Which approach would be better for handling the possibility that new record is being added with unique key that already exists?

Query db e.g.

if (!_db.MyTable.Any(mt => mt.UniqueCode == newRecord.UniqueCode))
  _db.MyTable.Add(newRecord);
else //handle response

or try to add it without checking and handle the error? e.g.

try 
}
  _db.MyTable.Add(newRecord);
}
catch (Exception e)
{
  //handle response
}

In the first approach the downside I see is that it requires 2 calls to db. (I left _db.SaveChanges() out), but I'v always felt that it's better to avoid exceptions. How is it? Which one would be better for performance? Is there a better way?

I think it's better to do exception handling in your application than a second round trip to the database. Considering what a call to the DB involves (everything from EF internals to DB internals and communication), exception handling doesn't sound so bad. But I think a performance measurement should be done in order to prove which is best. — Mihai Caracostea, Feb 16 '16 at 11:12

score 3 · Accepted Answer · answered Feb 16 '16 at 11:30

The second method is much, much, much better, because the first is not guaranteed to work in a multi-threaded environment. A new record could be added between the check and the insert, resulting in duplicate records or an exception.

In general, it is best to let the database validate the data when it can. The database engine guarantees uniqueness even in complicated scenarios that might be difficult to code at the application layer.

score 1 · Answer 2 · edited May 23 '17 at 12:23

Generally it is better to avoid situations where part of your working solution depends on a "catch all exceptions" scenario. It is preferable to avoid it if possible, so normally you should stick with querying the DB first.

You can come up on situations, where checking beforehand is not preferred. In such an event, you should handle the specific exception you anticipate first, which gives you "free hands" to handle any unexpected exceptions separately. You have to evaluate if this is such a situation.

If it is .. Then in your case you could handle the "unique constraint violation" exception like this:

try
{
   _db.MyTable.Add(newRecord);
}
catch (SqlException ex)
{
   if (ex.Number == 2627)
   {
     // Handle unique constraint violation.
   }
   else
   {
     // Handle the remaing SQL errors.
   }
}
catch (Exception e)
{
  // Handle any other non-SQL exceptions
}

For a list of possible exceptions with corresponding id's, you can run this query:

SELECT * FROM sys.messages
WHERE text like '%duplicate%' and text like '%key%' and language_id = 1033

Query was taken from Unique Key Violation in SQL Server

Unique column, checking for existing record, best approach

2 Answers2