Constraints and NULLs

This is a continuation of our series on check constraints. We are going to consider the use of check constraints on columns that allow NULLs. One good example of this is an EmployeeMiddleName column. Some people do not have middle names and other people have them but have declined to provide them. Also, when middle names are entered, they may be entered as only an initial. Sometimes the initial is followed by a period and sometimes it is not.

We’ve been asked to add a constraint against the MiddleName column in a table that has data already in it. It allows for NULLS for the MiddleName. Some rows have names, some have initials without periods and some have initials with periods. We want to disallow the use of periods after the initial (or name) on all new rows. So, we allow NULLs or names without periods.

This example was covered in the Udemy.com video called 70-461 Session 3: Querying Microsoft SQL Server 2012 by Philip Burton.

Employee Middle Name Example

Continuing with our example, here is the code for the constraint we want. Our constraint says: remove the period if one exists and if it the same then TRUE, or it is NULL. If either evaluates to TRUE, then it is okay. If it has no period or it is NULL, accept it.

alter table [dbo].[tblEmploy]
add constraint chkMiddleName check
(REPLACE([EmployeeMiddleName],'.','')  = [EmployeeMiddleName] or [EmployeeMiddleName] is null);

Our constraint fails when we try to create it (Execute). We get an error. We are unable to apply the constraint. We are stuck. We can solve this with the phrase with nocheck.

alter table [dbo].[tblEmploy] with nocheck
add constraint chkMiddleName check
(REPLACE([EmployeeMiddleName],'.','')  = [EmployeeMiddleName] or [EmployeeMiddleName] is null);

This one above will work. Now, whenever you add a new row or update a row, this constraint will apply. This constraint does allow the use of NULL as valid value for our column. So, if your column is nullable and you want to allow NULL in the future, but constrain some other part of the data, include the part or column is null in the constraint.