SQL Server – Adding Constraint to Table with Existing Data

The question:

What happens if a "NOT NULL" constraint is added to a table to a column that already has NULL values?

A simple example in SQL Server is a constraint to disallow NULLs.

Create a table and insert two rows, one not null and one null.

USE tempdb

CREATE TABLE dbo.OneNull (c1 INT)

INSERT dbo.OneNull ( c1 )
    VALUES  (0), (NULL) 

The ALTER TABLE syntax can direct what happens when you create the constraint. Using WITH NOCHECK will tell the engine (vroom vroom) not to check the column contents, but just to add the constraint for future rows.

ALTER TABLE dbo.OneNull WITH NOCHECK ADD CONSTRAINT ck_checkone CHECK (c1 IS NOT NULL) 

Adding this constraint runs successfully, but it means that the constraint is untrusted, and you lose certain query optimizations. For instance, if this were a Foreign Key, you would lose join elimination. For a column constraint, the optimizer can skip doing any work period if constraint information tells it that a search will yield no results.

But now this insert fails:

INSERT dbo.OneNull ( c1 )
    VALUES  (NULL) 

You can also direct the engine (beep beep) to check the column values while adding the constraint.

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 IS NOT NULL) 

Adding this constraint will fail.

If you add one with no formal direction (at least for column check constraints), the engine (screeeeech) will throw an error.

ALTER TABLE dbo.OneNull ADD CONSTRAINT ck_checkornocheck CHECK (c1 IS NOT NULL) 

An example of the type of optimization you can get with a trusted constraint is like so:

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 <= 1) 

INSERT dbo.OneNull ( c1 )
    VALUES  (1)

SELECT *
FROM dbo.OneNull AS onu
WHERE onu.c1 > 2
AND 1 = 1 --this is added to prevent a trivial plan

Rather than look for values at all, the engine (honk honk) knows that such a value can't exist because of the trusted constraint. Rather than put together a 'real' query plan and read any data, the optimizer short circuits and uses a Constant Scan operator to return NADA!