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.
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.
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.
- Using Regular Expressions with CHECK constraints [Link]
- Defining CHECK constraints on computed columns [Link]
Until we meet next time,