How to Check Unique Key Constraint

Question

I want check unique key constrain but I do not know which method to use. Which method is better? I use c#, EF, SQL Server

First Method:

    bool contactExists = _db.Contacts.Any(contact => contact.ContactName.Equals(ContactName));

if (contactExists)
{
    return -1;
}
else
{
    _db.Contacts.Add(myContact);
    _db.SaveChanges();
    return myContact.ContactID;
}

Second Method:

Handle in exception.

The third method: check with T-SQL

If  Exists  (
    Select  Name
        From    dbo.ContentPage
        Where   Name    = RTRIM(LTRIM(N'page 1'))
    )

Begin Select 'True' End Else Begin Select 'False' End

Your question looks similar to this one - http://stackoverflow.com/questions/18113418/ignore-duplicate-key-insert-with-entity-framework — Eugene Podskal, Jul 16 '14 at 06:54

score 3 · Accepted Answer · answered Jul 16 '14 at 06:55

I rarely check unique key constraints in code. It fact, it is really a waste of time if multiple clients can be updating data at the same time. Say you check to make sure you could add the employee 'Saeid Mirzaei' and found the key was not in use. So you add it. You can still get a dup key problem if someone else enters it in the meantime and you end up getting the exception anyway. You can handle the exception in TSQL or C# but you pretty much need to use exception handling for robust code.

score 1 · Answer 2 · answered Jul 16 '14 at 06:54

1

It depends on your requirements, but usually handling in a Exception is the best choice. This will be most efficient and reliable. I assume you mean by this the Exception that will be thrown because of the unique constraint.

answered Jul 16 '14 at 06:54

wvdz

16,251
4
53
90

score 0 · Answer 3 · answered Jul 16 '14 at 06:53

0

I prefer method 1, check in your code AND add UNIQUE constraint in your column.

answered Jul 16 '14 at 06:53

Iswanto San

18,263
13
58
79

score 0 · Answer 4 · answered Jul 16 '14 at 06:54

I suggest you that Create Unique index on Name column in SQL Server, and have instead of insert trigger for save RTRIM(LTRIM(Inserted.name)) column instead of name column on your table.

Also you can have client control for reduce network connection to your database.

How to Check Unique Key Constraint

4 Answers4