I continue to be surprised by the various practices that developers use to achieve the same result.
The common understanding with SQL Server DDL is that in most cases, there is only one method to implement a given design element. For example, to ensure that a column does not accept NULL values, the solution that comes to mind is to define it as a NOT NULL column.
Recently, I came across a trainee who proposed to implement the NOT NULL requirement for a given column in a way that’s not conventional – he proposed to use CHECK constraints. I had never heard of anyone using CHECK constraints to enforce a NOT NULL check, but I liked the thought process and hence wrote a quick prototype to demonstrate why this was not a good idea.
The following is how I explained why we don’t use CHECK constraints to enfore NOT NULL values (for sake of brevity, I am demonstrating with a table that has only one column).
USE tempdb ;
GO
--Safety Check
IF OBJECT_ID('dbo.NotNullCheckConstraint', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.NotNullCheckConstraint ;
END
GO
--Table kept short for sake of brevity
CREATE TABLE dbo.NotNullCheckConstraint ( Age INT ) ;
GO
ALTER TABLE dbo.NotNullCheckConstraint
ADD CONSTRAINT chk_AgeSpecified CHECK ( Age > 0 ) ;
GO
On most environments, though, this will not stop the user from entering NULLs. Let’s try it out:
USE tempdb;
GO
--Insert Test Data to prove that the CHECK constraint does not stop NULL values
INSERT INTO dbo.NotNullCheckConstraint ( Age )
VALUES ( NULL ),
( NULL ) ;
GO
--Select data from the table
SELECT Age
FROM dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/
The only real way to enforce a NOT NULL values via CHECK constraint is to alter the constraint to the following:
--Truncate the test table
TRUNCATE TABLE dbo.NotNullCheckConstraint;
GO
--Drop the old constraint
ALTER TABLE dbo.NotNullCheckConstraint
DROP CONSTRAINT chk_AgeSpecified ;
GO
--Create a new constraint
ALTER TABLE dbo.NotNullCheckConstraint
ADD CONSTRAINT chk_AgeSpecifiedWithNotNullCheck CHECK ( Age IS NOT NULL
AND Age > 0 ) ;
GO
Let us now try to insert NULL values into the table:
USE tempdb ;
GO
--Insert Test Data to prove that the new CHECK constraint did stop NULL values
INSERT INTO dbo.NotNullCheckConstraint ( Age )
VALUES ( NULL ),
( NULL ) ;
GO
--Select data from the table
SELECT Age
FROM dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
*/
We encounter the following error message:
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint “chk_AgeSpecifiedWithNotNullCheck”. The conflict occurred in database “tempdb”, table “dbo.NotNullCheckConstraint”, column ‘Age’.
The statement has been terminated.
(0 row(s) affected)
In this case, the CHECK constraint did indeed prevent us from inserting NULL values into the table.
But, is this a good idea?
The answer, in my humble opinion is – No.
And, I have two reaons for it:
- CHECK constraints are ideally suited to enforce business rules and business logic rather than data integrity
- They are evaluated after the SQL Server database engine is sure that the input values do not violate any properties or boundaries of the defined column
- I even wrote an article about the constraint execution sequence on SQLServerCentral.com here – Leveraging Constraint Evaluation Sequence in SQL Server. The examples in the article demonstrate how the NOT NULL constraint is evaluated before CHECK constraints
- A CHECK constraint can be disabled by a user having sufficient priviledges thereby allowing the application to insert NULL values – a NOT NULL column definition on the other hand would remain in effect
Here’s a quick check for point #2 (disabling a CHECK constraint may allow to enter invalid data).
USE tempdb;
GO
--Now, disable the constraint
ALTER TABLE dbo.NotNullCheckConstraint
NOCHECK CONSTRAINT chk_AgeSpecifiedWithNotNullCheck;
GO
--Insert Test Data to prove that the new CHECK constraint did NOT stop NULL values
INSERT INTO dbo.NotNullCheckConstraint ( Age )
VALUES ( NULL ),
( NULL ) ;
GO
--Select data from the table
SELECT Age
FROM dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/
/********************************************************************************/
USE tempdb ;
GO
--Now, re-enable the constraint
--Notice no error is reported
ALTER TABLE dbo.NotNullCheckConstraint
CHECK CONSTRAINT chk_AgeSpecifiedWithNotNullCheck;
GO
--Insert Test Data to prove that the new CHECK constraint did stop NULL values
--This will report an error
INSERT INTO dbo.NotNullCheckConstraint ( Age )
VALUES ( NULL ),
( NULL ) ;
GO
--Select data from the table - results from earlier run are still available
SELECT Age
FROM dbo.NotNullCheckConstraint ;
GO
/*
Results:
--------
Age
--------
NULL
NULL
*/
Summary
Roughly defined, patterns are common solutions to common problems. There is a reason why Patterns and Practices were developed and this a classic example. A CHECK constraint can be used to enforce a NOT NULL check, but it is not a normal pattern and the reaons are:
- CHECK constraints are evaluated after NOT NULL validation
- CHECK constraints may be disabled allowing the user to enter invalid data into the table
Further Reading
- Leveraging Constraint Evaluation Sequence in SQL Server [Link]
- Using Regular Expressions with CHECK constraints [Link]
- Defining CHECK constraints on computed columns [Link]
- Defining “complex” CHECK constraints [Link]
Until we meet next time,
SQLTwins by Nakul Vachhrajani 