Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 332
oscar.fimbres Avatar asked Nov 30 '11 05:11

oscar.fimbres


People also ask

How do I create a custom error message in SQL Server?

Therefore, when a system error occurs, SQL Server will log a system error and may take actions to fix the error. Custom errors, on the other hand, are generated by T-SQL custom codes based on your code or business logic. To add a custom error message to sys. messages, the stored procedure sp_addmessage is used.

How do you modify an existing check constraint in SQL?

Modifying Properties of a Constraint Use the following syntax for the ALTER TABLE command to modify the properties of a constraint: ALTER TABLE <table_name> { ALTER | MODIFY } { CONSTRAINT <name> | PRIMARY KEY | { UNIQUE | FOREIGN KEY } (<column_name>, [ ... ] ) }

How do you modify an existing check constraint?

You have to drop it and recreate it, but you don't have to incur the cost of revalidating the data if you don't want to. alter table t drop constraint ck ; alter table t add constraint ck check (n < 0) enable novalidate; Show activity on this post. constraints are always in place.

Can we define check constraints using alter table command?

The syntax for creating a check constraint in an ALTER TABLE statement in SQL Server (Transact-SQL) is: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition); table_name. The name of the table that you wish to modify by adding a check constraint.


1 Answers

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)
like image 153
Luke Avatar answered Oct 27 '22 00:10

Luke