Message returned when a check constraint violated

• appolden

  Mr or Mrs. 500

  Points: 567

  More actions

  November 18, 2003 at 10:29 am

  #83485

  I have several check constraints in place. E.g. len (Field1) = 6

  Is there a way of specifying a user-defined message when a particular constraint is violated? So for each constraint, there will be a unique message that relates to the violation. Can any thing other than triggers achieve this?

  NB I do not want to change the message in sysmessages.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 18, 2003 at 10:49 am

  #482482

  Form the section "Triggers Compared to Constraints" in BOL Article "Enforcing Business Rules with Triggers"

  quote:

  ---

  Constraints can communicate about errors only through standardized system error messages. If your application requires (or can benefit from) customized messages and more complex error handling, you must use a trigger.

  ---

• jcool

  SSC Eights!

  Points: 863

  More actions

  November 19, 2003 at 1:33 am

  #482562

  You can put a short message inside the name of CK. When a user reads a violation message, he will see that message too. I do it like this:

  CK_Table_1_[_Field_length_must_be_6_]

  -where CK= Check Constraint, Table = table name, 1 = the number of CK on this table, if you have several, you only need the user to tell you the number, not the whole message, [] = message inside paretheses.

  Try it, if a user carefully looks at the error message he will see your note.

  That's as far as it goes without triggers.