'Create custom error message in check constraints in SQL SERVER 2008

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.

sql-serverdatabasesql-server-2008check-constraints


Solution 1:[1]

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 ?

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 gbn

Related Questions