Create custom error message in check constraints in SQL SERVER 2008

Question

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.

Interaction

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 – Plaided

Answer 1

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)

Mineraloid

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. – Recessional

Is it possible in MySQL? – Gerger

Answer 2

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:

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 😄

Corrincorrina

That is really ugly abuse of a quoted identifier, but +1 for the atrocious solution :) – Cauthen

Here 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... – Corrincorrina

One of the most beautiful ugly hacks I have ever seen. – Recessional

This is definitely a most terrible and awesome thing at the same time. – Tragacanth

Answer 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)
....

Bobbie