T-SQL: Error Handling for CHECK Constraints
Introduction
In the former article about CHECK constraints, we did not cover how to handle the errors when a CHECK constraint violates. In this article, we cover this important topic. It has worth to take a look at that article, if it needed. All Code samples in this article are downloadable from this link.
Problem
We can rapidly jump into the main problem using a sample. Assuming that we have a Book table that has two date columns one for writing date and other for publish date. We want to apply a date validator to avoid inserting the writing dates that are greater than publish date in each row using the next code:
IF OBJECT_ID('dbo.book', 'u') IS NOT NULL DROP TABLE dbo.Book
go
CREATE TABLE dbo.Book
(
BookId INT NOT NULL ,
WritingDate DATE NULL ,
publishDate DATE NULL ,
CONSTRAINT Pk_Book PRIMARY KEY CLUSTERED ( BookId ASC )
)
GO
ALTER TABLE dbo.Book WITH CHECK
ADD CONSTRAINT DateValidator CHECK ( WritingDate > publishDate )
GO
Now, we can test what will happen if the violation occurs. To do this, we can use the next code and we will face with an error message like the following image:
INSERT dbo.Book
( BookId, WritingDate, publishDate )
VALUES ( 1, GETDATE(), GETDATE() + 1 )
Solution
As illustrated in the above picture, the error message is not so clear. It is good to know that error happened on which object, but with no reason. We can use two workaround to make it clearer.
Custom Error Table
As it is it highlighted in yellow color in the above picture, whenever a CHECK constraint violates we get the same error message number that is 547. We can use this error number, send it to a function that can make a good error message. In the error message that sows in the above picture, we can see that we have another great pretext which is the own CHECK constraint name which is unique in the whole database. All these information lead us to use a user table that helps us to create good messages. We can start doing this by the next code:
CREATE TABLE CustomError
(
ObjectName NVARCHAR(128) PRIMARY KEY ,
ErrorMessage NVARCHAR(4000)
);
GO
INSERT dbo.CustomError
VALUES
( N'DateValidator',
N'Writing date must be greater than publish date.' ) ;
In the above code, we also insert a new good message as equivalent to constraint name. Now we can write a function to use the good message whenever the error occurs. One simple implementation is like the next code. You can change it as you wish to fit your requirements:
CREATE FUNCTION dbo.ufnGetClearErrorMessage1()
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE() ;
DECLARE @ErrNum INT = ERROR_NUMBER() ;
DECLARE @ClearMessage NVARCHAR(4000) ;
IF @ErrNum = 547
BEGIN
/*--how to find constraint name:
SELECT CHARINDEX('"', @Msg) ,
CHARINDEX('.', @Msg) ,
RIGHT(@Msg,LEN(@Msg) - CHARINDEX('"', @Msg)) ,
LEFT(RIGHT(@Msg,LEN(@Msg) - CHARINDEX('"', @Msg)), CHARINDEX('"', RIGHT(@Msg,LEN(@Msg) - CHARINDEX('"', @Msg))) - 1)
*/
DECLARE @ObjectName NVARCHAR(128)
SELECT @ObjectName = LEFT(RIGHT(@Msg,LEN(@Msg) - CHARINDEX('"', @Msg)), CHARINDEX('"', RIGHT(@Msg,LEN(@Msg) - CHARINDEX('"', @Msg))) - 1)
SELECT @ClearMessage = @Msg + CHAR(13) + ce.ErrorMessage
FROM dbo.CustomError AS ce
WHERE ce.ObjectName = @ObjectName ;
END
ELSE
SET @ClearMessage = @Msg ;
RETURN @ClearMessage ;
END
Now, we can use this function to get the good message. The next code shows how to use this function in our code:
BEGIN TRY
INSERT dbo.Book
( BookId, WritingDate, publishDate )
VALUES ( 1, GETDATE(), GETDATE() + 1 )
END TRY
BEGIN CATCH
DECLARE @Msg NVARCHAR(4000) = dbo.ufnGetClearErrorMessage1();
THROW 60001, @Msg, 1;
END CATCH
Naming Convention
Other solution is using a specific naming convention. Like the previous solution we can use the error number to identify that the error occurred when a CHECK constraint violated. But in this solution we will use a naming convention instead of using a user error table. Again, we create a function to clear the error message. We could use many user defined conventions. But in this article we see one sample and of course you can create your own one. The following code changes the CHECK constraint name to new one:
--drop old CHECK constraint
ALTER TABLE dbo.Book
DROP CONSTRAINT DateValidator
GO
--add new CHECK constraint
ALTER TABLE dbo.Book WITH CHECK
ADD CONSTRAINT C_Book_@Writing_date_must_be_greater_than_publish_date CHECK ( WritingDate > publishDate )
GO
Now, we can create a new function that will fix the error message based on the characters after the @-sign character and replaces the underline characters with space characters to make it a meaningful and readable error message. It is obvious that this is a user defined naming convention that have to be used in the whole database for all CHECK constraints implemented by all developers. The next code creates this function:
CREATE FUNCTION dbo.ufnGetClearErrorMessage2()
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE() ;
DECLARE @ErrNum INT = ERROR_NUMBER() ;
DECLARE @ClearMessage NVARCHAR(4000) ;
IF @ErrNum = 547
BEGIN
/*--how to find @ClearMessage:
SELECT @msg ,
CHARINDEX('@', @msg) ,
RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)) ,
CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) ,
LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ) ,
REPLACE(LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ), '_', SPACE(1)) + '.'
*/
SELECT @ClearMessage = @Msg + CHAR(13) +
REPLACE(LEFT(RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg)), CHARINDEX('"', RIGHT(@msg, LEN(@msg) - CHARINDEX('@', @msg))) - 1 ), '_', SPACE(1)) + '.'
END
ELSE
SET @ClearMessage = @Msg ;
RETURN @ClearMessage ;
END
Now, we can use this function to get the good message. The next code shows how to use this function in our code:
BEGIN TRY
INSERT dbo.Book
( BookId, WritingDate, publishDate )
VALUES ( 1, GETDATE(), GETDATE() + 1 )
END TRY
BEGIN CATCH
DECLARE @Msg NVARCHAR(4000) = dbo.ufnGetClearErrorMessage2();
THROW 60001, @Msg, 1;
END CATCH
Conclusion
Using these two solutions makes the error message clearer. Such good error messages tell us why it occurs in addition to where it happens. Moreover, we can use stored procedures instead of using functions.