Create Error Messages for Constraints

You can use sp_addmessage to create error messages, then use sp_bindmsg to bind them to constraints.

For example:

sp_addmessage 25001, 
     "The publisher ID must be 1389, 0736, or 0877"
sp_bindmsg my_chk_constraint, 25001
insert my_publishers values 
     ("0000", "Reject This Publisher")

Msg 25001, Level 16, State 1:
Server ‘snipe’, Line 1:
The publisher ID must be 1389, 0736, or 0877
Command has been aborted.

To change the message for a constraint, bind a new message. The new message replaces the old message.

Unbind messages from constraints using sp_unbindmsg; drop user-defined messages using sp_dropmessage.

For example:

sp_unbindmsg my_chk_constraint
sp_dropmessage 25001

To change the text of a message but keep the same error number, unbind it, drop it with sp_dropmessage, add it again with sp_addmessage, and bind it with sp_bindmsg.