15

I'd like to see the ability to attach custom error messages to CONSTRAINT objects, specifically CHECK constrints. Either directly or via a custom error number in sysmessages.

I've seen developers have to create triggers. I think that's not a good reason to implementing it.

I'm using SQL SERVER 2008.

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
oscar.fimbres
  • 1,145
  • 1
  • 13
  • 24
  • I'm afraid this is not possible. The only way you could do this would be to have a custom error message and handle the constraint via a trigger, in which you could then raise an error using your custom message – marc_s Nov 30 '11 at 06:10

3 Answers3

18

You could name your constraint with a user message.

For Example:

ADD CONSTRAINT 
[Foo cannot be greater than Bar. Please be sure to check your foos and bars next time.] 
CHECK (foo <= Bar)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Luke
  • 196
  • 1
  • 3
  • 3
    While helpful, this still does not allow embedding of variables in a custom message. I would have liked to output the values in the error message that I use in the logic of my check constraint. – Jacques Bosch May 18 '18 at 05:41
8

I know this is an old post, but I've found something that may make it a bit easier to provide clearer error messages for check constraints to the end-user: the names of check constraints can include carriage returns and line feeds, so the error message can be made a bit easier to see.

E.g. creating the following constraint produces the error message below. (the blank lines between the [ and ] are intentional i.e. they are part of the constraint name.)

ALTER TABLE dbo.Sales WITH CHECK ADD CONSTRAINT [


ERROR:
You have stupidly entered a negative selling price. Please report to detention.


] CHECK ([SellingPrice] >= 0.00)
GO

And when this constraint fails, the resulting message is:

enter image description here

I tried putting markup in the error message (i.e. constraint name), like <b>message</b> and *message*, but to no avail. And it may be possible, but really unwieldy, to use this for foreign key constraints as well. I haven't tried it.

So it's not a 100% solution, but hopefully easier for the user to see the intended error message.

Edit (2022-02-09): Since database object names are stored using the sysname data type (search for 'sysname' on this page), they cannot be longer than 128 characters. Use short error messages

Reversed Engineer
  • 1,095
  • 13
  • 26
  • 5
    That is really ugly abuse of a quoted identifier, but +1 for the atrocious solution :) – Gordon May 16 '16 at 18:00
  • [Here](http://social.technet.microsoft.com/wiki/contents/articles/29187.t-sql-error-handling-for-check-constraints.aspx) is an interesting TechNet article about how to create meaningful error messages just in T-SQL. They use a table containing constraint names and error messages. To make it totally seamless though, you have to use a TRY / CATCH block wherever you INSERT, UPDATE or DELETE, and want a meaningful message if a constraint makes it fail. However a similar principle could no doubt be used in front-end code... – Reversed Engineer Jul 25 '16 at 12:45
  • 4
    One of the most beautiful ugly hacks I have ever seen. – Jacques Bosch May 18 '18 at 05:39
  • 2
    This is definitely a most terrible and awesome thing at the same time. – Daniel Young Jun 26 '18 at 17:59
3

You can't directly

A CHECK constraint fails with a standard error message. You could use a TRY/CATCH block to parse the error and throw your own (RAISERROR) or use a trigger.

I'd check first so it doesn't fire, either is SQL or in client code. And of course you leave the constraint there to protect data integrity

So if you have a constraint

ALTER TABLE MyTable WITH CHECK 
    ADD CONSTRAINT CK_MyTable_foobar CHECK (@foo <= @Bar)

You run the following SQL code or equivalent in your client code:

...
IF @foo > @bar
    RAISERROR ('foo (%i) can not be greater than bar (%i)', 16, 1, @foo, @bar)

INSERT MyTable (foo, bar) VALUES (@foo, @bar)
....
gbn
  • 422,506
  • 82
  • 585
  • 676