#0298 – SQL Server – complex CHECK constraints


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s