Primary vs Unique key violation exception handling

Question

I'd like to catch SQL exception and differ between primary key violation and unique key violation. These two types of exceptinons returns the same ErrorCode 2627.

try
{

}
catch (SqlException ex)
{
    if (ex.Number == 2627)
    {
        throw new UniqueOrDuplicateKeyException("Unique key or Primary key duplication")
    }
}

It is nice but I'd like to throw UniqueKeyException or PrimaryKeyException. I know possibility to recognize which exception to throw, but it is parsing error message that is starting with 'Violation of UNIQUE KEY constraint' or 'Violation of PRIMART KEY constraint'. Of course, I'd like to avoid this option.

Another possibility is to do it directly in my stored procedure but I have a lot of stored procedures and it coul'd be quite annoying to add it everywhere.

Do you know some possiblities how to handle this issue in elegant way?

score 3 · Answer 1 · edited Apr 13 '17 at 12:42

3

I can't see a way of differentiating other than the error message string, viz:

PK Violation:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Foo'. Cannot insert duplicate key in object 'dbo.Foo'.

Unique Constraint Violation

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'U_Foo'. Cannot insert duplicate key in object 'dbo.Foo'.

If its in your control to do so, you could consider changing the Unique Constraint to a Unique Index?

If so, you could then detect between 2601 for a unique index violation, and 2627 for the PK Violation.

edited Apr 13 '17 at 12:42

Community

1
1

answered Jan 07 '15 at 12:52

StuartLC

104,537
17
209
285

As an aside, I can imagine the look on the face of just about every DBA I've met when explaining the rationale as to *why* the UKC is suddenly a UI... – StuartLC Jan 07 '15 at 13:27
I don't think thad exceptions handling is sufficient reason to change constraints to indexes in all tables. – y0j0 Jan 07 '15 at 13:33

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

1

Dont know why I am not able to post this as an answer, but asking me to post this as a comment. This has been answered before. You can get your answer here.

In a nutshell, you could use the hack below:

if (ex.Message.Contains("Unique")) // It is an unique key violation.

edited May 23 '17 at 11:54

Community

1
1

answered Jan 07 '15 at 13:10

SouravA

5,147
2
24
49

Thank you, but as I wrote I'd like to avoid this solution. – y0j0 Jan 07 '15 at 13:30

Primary vs Unique key violation exception handling

2 Answers2