Constraint Violation

[Can Balioglu]

I have a user table which has two columns with unique constraints (email and
username). I have a stored procedure which inserts a new user into my table.
When a constraint violation during the sp execution occurs, I'm trying to
programmaticaly determine which of the constraints caused the violation.

But here comes the problem. Both of the contraints generate exactly the same
error except the message string. Both errors have the number 2627, state 2
and linenumber 22. How can I determine which of the constraints caused the
violation?

PS: I'm programming in Ado.Net. During exception handling I need to
determine the cause of the error from the SqlException instance, but as I
said they both generate exactly the same error except the message string
which is not reliable.

Thanks for replies.

 

[oj]

The error is generated from the same template in sysmessages. You will have
to parse the message string to determine the source.

 

[Louis Davidson]

I have been made fun of for my naming conventions, but what you need to do
is name constraints in such a way that you have a key that tells what has
happened, then write an error handler in you code to translate (weak
solution, but the best that they have given us so far)

Just add the name to the unique constraint definition:

alter table TABLENAME add constraint AltKey_TableName_FieldName1_Fieldname2
UNIQUE (fieldName1, fieldName2)

Or whatever, just something to make it clear to you what the error is. I
use something like this for all constraint types.

--
----------------------------------------------------------------------------
-----------
Louis Davidson ([email protected])
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

 

[Can Balioglu]

As you said, it seems naming constraints in a unique manner is the most
acceptable solution. Thanks a lot for both answers.