Post reply

CHECK CONSTRAINTS

  • July 17, 2001 at 7:26 am

    #77644

    Is there any way to customize the error message of a Check Constraint? SysMessage 547 is quite complex for a simple User.

  • July 18, 2001 at 10:46 pm

    #417178

    I pulled this from T-SQL help file:

    . Use @@ERROR to detect a specific error

    This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

    USE pubs

    GO

    UPDATE authors SET au_id = '172 32 1176'

    WHERE au_id = "172-32-1176"

    IF @@ERROR = 547

    print "A check constraint violation occurred"

  • July 18, 2001 at 11:00 pm

    #417180

    To expand upon what Leon has already written, you can use RAISERROR to customize your message. For instance:

    USE pubs

    GO

    UPDATE authors SET au_id = '172 32 1176'

    WHERE au_id = '172-32-1176'

    IF @@ERROR = 547

    RAISERROR('The author ID must be in the form of XXX-XX-XXX to be valid. Please try your update again.', 16, 1)

    The one issue here is that error 547 will be returned to the client anyway. Therefore, if it usually good if you're going to raise a custom error in SQL Server to trap for the error condition prior to executing the query and using RAISERROR then.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply