79

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.
}

?

Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
User
  • 3,244
  • 8
  • 27
  • 48

3 Answers3

135

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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    @gbn What is the difference between unique constraint and unique index? Don't both enforce indices? – Zameer Ansari Feb 18 '16 at 15:36
  • 2
    https://technet.microsoft.com/en-us/library/aa224827(v=sql.80).aspx - In summary, we can safely conclude that there's no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database. – bfhd Feb 07 '17 at 21:07
  • @bfhd that is for SQL Server 2000. Very old. There are differences in key vs constratints such a INCLUDE and WHERE clauses. I never use constraints, too inflexible, – gbn Oct 07 '18 at 18:54
  • 1
    I want to add that `message_id` is reused for all languages. So even if you're not using English, the message returned will be different, but the `message_id` stays the same. – Hp93 Sep 21 '21 at 05:15
22

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 ));
    }
jhilden
  • 12,207
  • 5
  • 53
  • 76
4

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

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • What do you mean by "within an approximation"? – User Jun 26 '11 at 11:53
  • @User - all I meant was that I can't think of anything else that will complain about duplicate keys, but the message is unfortunately parameterized on the type of constraint. – Damien_The_Unbeliever Jun 26 '11 at 11:56
  • @Damien_The_Unbeliever, link to [this page] leads to content of Microsoft Docs. was it intended? – ASh Jun 18 '20 at 14:45