We were recently working on an implementation and had to introduce a couple of CHECK constraints to maintain logical integrity of the database with respect to the business rules. As we were going through the table design, one of my colleagues asked a very interesting question:

Most of our constraints are quite simple, mostly range checks which involve a maximum of two columns. Is it possible to have a complex CHECK constraint which involves more than two columns?

The answer, quite simply is Yes! CHECK constraints can be complex, provided they continue to be an expression that evaluates to a Boolean value.

Demo

I will take a sample table derived from my post earlier in the week:

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

--Create the temporary table
CREATE TABLE dbo.ComplexCheckConstraint
    (
      StartDate DATE,
      StartTime TIME,
      EndDate DATE,
      EndTime TIME,
    ) ;
GO

Now, let us create a complex CHECK constraint on the table:

USE tempdb;
GO
--Add the complex constraint
ALTER TABLE dbo.ComplexCheckConstraint
    ADD CONSTRAINT chk_EndDate CHECK ( (EndDate > StartDate) 
                                       OR (EndDate = StartDate AND 
                                           EndTime > StartTime) 
                                     );
GO

Note that we have five operators (3 comparison operators and 2 logical operators) and four columns (EndDate, StartDate, EndTime and StartTime). To check if the constraint works or not, let us attempt to insert some invalid data into the test table:

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

We get the following error, proving that the complex table constraint was created and is in effect.

Msg 547, Level 16, State 0, Line 2

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

The statement has been terminated.

Attempting to insert valid data works fine and does not produce a violation.

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 – irrespective of the complexity of the requirement.

Further Reading:

• Using Regular Expressions with CHECK constraints [Link]
• Defining CHECK constraints on computed columns [Link]

Until we meet next time,

Be courteous. Drive responsibly.