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,
Pingback: #0385 – SQL Server – Query Development and NULL values | SQLTwins by Nakul Vachhrajani
More importantly, a NOT NULL constraint can be used to make statements about data distribution in the column, it guarantees that a single-column index on the column will hit every record (which is not the case if the column can be null!) and thus gives the optimizer valuable information. A check constraint does no such things, it is ignored for query optimization purposes.
LikeLike