Unique Key Violation in SQL Server - Is it safe to assume Error 2627?

Sql Server-2005Unique Constraint

Sql Server-2005 Problem Overview


I need to catch violation of UNIQUE constraints in a special way by a C# application I am developing. Is it safe to assume that Error 2627 will always correspond to a violation of this kind, so that I can use

if (ThisSqlException.Number == 2627)
{
    // Handle unique constraint violation.
}
else
{
    // Handle the remaing errors.
}

?

Sql Server-2005 Solutions


Solution 1 - Sql Server-2005

2627 is unique constraint (includes primary key), 2601 is unique index

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

Solution 2 - Sql Server-2005

Here is a handy extension method I wrote to find these:

    public static bool IsUniqueKeyViolation(this SqlException ex)
    {
        return ex.Errors.Cast<SqlError>().Any(e => e.Class == 14 && (e.Number == 2601 || e.Number == 2627 ));
    }

Solution 3 - Sql Server-2005

Within an approximation, yes.

If you search the MS error and events site for SQL Server, error 2627, you should hopefully reach this page1, which indicates that the message will always concern a duplicate key violation (note which parts are parameterized, and which not):

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.

1As @2020-06-18, Database engine errors and events would be the correct page to go to

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionUserView Question on Stackoverflow
Solution 1 - Sql Server-2005gbnView Answer on Stackoverflow
Solution 2 - Sql Server-2005jhildenView Answer on Stackoverflow
Solution 3 - Sql Server-2005Damien_The_UnbelieverView Answer on Stackoverflow