#0297 – SQL Server – Defining CHECK constraints on computed columns

Most enterprise applications have something a module similar to a calendar or a planner which tracks information like the start and end date/times of a particular task and/or activity. Over the years, I have seen logical data inconsistencies (especially if the application has been around for a while) wherein the end date/time is sometime earlier than the start end/time. For example, a task may start on September 23, 2013 10AM and finish on September 23, 2013 9AM – which clearly, is an issue.

If these systems were re-engineered after the advent of SQL Server 2008, the date/time value is actually stored in separate fields – a DATE column and a TIME column and a computed column is available that contains the date/time representation. Here’s an example:

USE tempdb ;
GO
--Safety Check
IF OBJECT_ID('dbo.ComputedColumnConstraintCheck', 'U') IS NOT NULL 
    DROP TABLE dbo.ComputedColumnConstraintCheck ;
GO

--Create the temporary table
CREATE TABLE dbo.ComputedColumnConstraintCheck
    (
      StartDate DATE,
      StartTime TIME,
      StartDateTime AS ( CAST(StartDate AS DATETIME)
                         + CAST(StartTime AS DATETIME) ),
      EndDate DATE,
      EndTime TIME,
      EndDateTime AS ( CAST(EndDate AS DATETIME) 
                       + CAST(EndTime AS DATETIME) )
    ) ;
GO

Normally, the solution to prevent logical data inconsistencies is to have a CHECK constraint on the column. But, can a CHECK constraint be defined on a computed column?

Let us attempt to create the required CHECK constraint for our example:

USE tempdb ;
GO
--Attempt to add the Check Constraint    
ALTER TABLE dbo.ComputedColumnConstraintCheck
ADD CONSTRAINT chk_EndDateTime CHECK ( EndDateTime > StartDateTime ) ;
GO

The following error message is encountered and the constraint is not created when we attempt to execute the SQL statement provided above:

Msg 1764, Level 16, State 1, Line 2

Computed Column ‘StartDateTime’ in table ‘ComputedColumnConstraintCheck’ is invalid for use in ‘CHECK CONSTRAINT’ because it is not persisted.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint. See previous errors.

The first error message is when the error actually occurred and hence that is the message of interest. The message quite clearly tells us that the CHECK constraint definition was invalid because the underlying column was not persisted. Because constraints are in-turn implemented as indexes, it makes sense to have the column as computed.

So, let us convert the column to a computed column. We have at least two methods to convert a non-persisted computed column to a persisted one:

Create an index using the computed column
Drop the column and re-create it as persisted

For the purposes of this demo, we will be using the 2nd method (drop-and-recreate the column). Once the persisted columns are created, we will attempt to create the CHECK constraint again:

USE tempdb;
GO
--Now, alter the computed column to make it persisted
ALTER TABLE dbo.ComputedColumnConstraintCheck
    DROP COLUMN StartDateTime;
ALTER TABLE dbo.ComputedColumnConstraintCheck
    ADD StartDateTime AS ( CAST(StartDate AS DATETIME) 
                           + CAST(StartTime AS DATETIME) ) PERSISTED;
GO

ALTER TABLE dbo.ComputedColumnConstraintCheck
    DROP COLUMN EndDateTime;
ALTER TABLE dbo.ComputedColumnConstraintCheck
    ADD EndDateTime AS ( CAST(EndDate AS DATETIME) 
                         + CAST(EndTime AS DATETIME) ) PERSISTED;
GO

--Attempt to add the Check Constraint    
ALTER TABLE dbo.ComputedColumnConstraintCheck
ADD CONSTRAINT chk_EndDateTime CHECK ( EndDateTime > StartDateTime ) ;
GO

Now that the constraint has been created, let us attempt to insert some data which violates the constraints.

USE tempdb;
GO
--Attempt to insert some invalid data
INSERT  INTO dbo.ComputedColumnConstraintCheck
        (
          StartDate,
          StartTime,
          EndDate,
          EndTime
        )
VALUES  (
          '2013-09-19',
          '08:00',
          '2013-09-19',
          '07:00'
        ) ;
GO

We immediately encounter the following error and the INSERT fails:

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "chk_EndDateTime". The conflict occurred in database "tempdb", table "dbo.ComputedColumnConstraintCheck".

The statement has been terminated.

Summary

CHECK constraints help to maintain logical consistencies in the database. They can help validate data with respect to a given pattern and also help in ensuring that the data in a column is well within the acceptable limits as defined by the business. Having the ability to enforce business validations via the use of CHECK constraints on computed columns is an added advantage in making the database design robust.

Further Reading: