#0322 – SQL Server – Why is it not a good idea to implement NOT NULL check as a CHECK constraint


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:



  1. 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

  2. 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:



  1. CHECK constraints are evaluated after NOT NULL validation

  2. 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,


Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0322 – SQL Server – Why is it not a good idea to implement NOT NULL check as a CHECK constraint

  1. Pingback: #0385 – SQL Server – Query Development and NULL values | SQLTwins by Nakul Vachhrajani

  2. Volker K.

    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.

    Like

    Reply

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