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?

Community · Accepted Answer · 2017-04-13 12:42:36Z

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.

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...
I don't think thad exceptions handling is sufficient reason to change constraints to indexes in all tables.

Community · Accepted Answer · 2017-05-23 11:54:12Z

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.

Collectives™ on Stack Overflow

Primary vs Unique key violation exception handling

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related