Custom Error Messages in Check Constraint Violation

Introduction

When a CHECK constraint of Table objects violates it gives an error message, showing that the check constraint is violated. One of the request that I get from a developers, that he want to provide the customized Error Message when the CHECK constraint violates.

Is it Possible?

In my point of view it is NOT possible. When the CHECK constraint violates it show the system error message like this.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_STDHIGHT_HIGHT". The conflict occurred in database

"PRACTICE_DB", table "dbo.tbl_PRODSTATUS".

The statement has been terminated.

So how can we customized it

We can use TRY,  CATCH and RAISERROR() to solve this problem. Here I am trying to demonstrate it.

Step-1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_PRODSTATUS', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_PRODSTATUS];

   END

GO

CREATE TABLE [dbo].[tbl_PRODSTATUS]

    (

      IDNO        INT  NOT NULL IDENTITY PRIMARY KEY,

      STDHIGHT    INT  NOT NULL,

      HIGHT       INT  NOT NULL

    );

GO

Step-2 [ Create CHECK Constraint ]

ALTER TABLE  [dbo].[tbl_PRODSTATUS]

ADD CONSTRAINT CHK_STDHIGHT_HIGHT

      CHECK(STDHIGHT>=HIGHT);

Step-3 [ Making Custom Error Message ]

--- Custom Error Handeling ---

EXEC sp_addmessage

     @msgnum   = 50020,

     @severity = 1,

     @msgtext  = 'Can not Insert records in Table : %s as %s can not >= columns %s';

If you need the reference of RAISERROR() function, please check my previous article.

http://www.sqlknowledgebank.blogspot.in/2014/10/raiserror.html

Step-4 [ TRY,  CATCH and RAISERROR() to Solve ]

BEGIN

     BEGIN TRY

           DECLARE   @v_TblName   VARCHAR(50),

                     @v_ColName1  VARCHAR(50),

                     @v_ColName2  VARCHAR(50);

           SET @v_TblName= '[dbo].[tbl_PRODSTATUS]';

           SET @v_ColName1 ='[HIGHT]';

           SET @v_ColName2 ='[STDHIGHT]';  

           INSERT INTO  [dbo].[tbl_PRODSTATUS]

                (STDHIGHT, HIGHT)

           VALUES(30, 40);

     END TRY

     BEGIN CATCH

           RAISERROR(50020, 1, 1, @v_TblName, @v_ColName1, @v_ColName2);

     END CATCH

END

Step-5 [ Execute analyze the Output ]

(0 row(s) affected)

Can not Insert records in Table : [dbo].[tbl_PRODSTATUS] as [HIGHT] can not >= columns [STDHIGHT]

Msg 50020, Level 1, State 1

Hope you like it.

Posted by: MR. JOYDEEP DAS