How to add a custum message to a table constraint

Hi Roy,

Edit: Vote here for this idea:

-----Original message

That is indeed currently not possible. Check contraints are created on tables and show the [translated] database error message.

To show a custom message you can use the Default procedure and/or Triggers. In there you can determine when to give the message to the user and adding more info. The function tsf_send_message is a good way to go as it can show a translated message and it can show more information.

In the project I work on, we use it in the default procedure as we always want to notify the user immediately if some value is not permitted. In code, the Default looks something like this:

if @date_from > @date_until
begin
   declare @error_text varchar(500)
   select @error_text = null --Optional, see docs for possibilities

   exec tsf_send_message 'date_from_past_date_until', @error_text, 0

   select @date_from = null
end

*Note, comparing @date_from and @date_until only works if both have the Datetime data type as Datetime2 cannot be compared with each other.

In the error text you can show any value you like, such as the value of the date from column. In the translation of the Message, use {date_from} to place the value in that spot. {0} can be used to place text or a translation of a column for example.

You could also lead the user to the field that is incorrect by using the @cursor_to_col_id = 'date_from’. To highlight the field, you could use the Conditional layout by comparing the date_from column with the date_until column. If then the date_from column has a bigger value, it will be visible. 

If you would like Messages in combination with Constraints, feel free to create a topic in the Ideation section. I like the idea and will certainly vote for it.

Kind regards,
Mark Jongeling