The CHECK Constraint That Lets NULL Through
You add a CHECK constraint to stop negative quantities.[1] CHECK (quantity >= 0), because a warehouse count should never be below zero. You test it by inserting -5 and it gets rejected, so the constraint works. Months later you find rows where quantity is NULL, and you had assumed the same constraint would keep those out too. It did not. The CHECK accepted them.
This is Part 4 of The UNKNOWN Problem, a series on NULL traps in T-SQL. The earlier parts were about rows a query dropped. This one is about a row a CHECK constraint accepts when you expected it to reject.
Here is the setup, using the inventory table from the series schema. The quantity column is nullable in the schema, and the question this post is really about is whether that was the right call once a range CHECK is on the column.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE dbo.inventory ( product_id int NOT NULL , warehouse_id int NOT NULL , quantity int NULL , bin varchar(15) NULL , CONSTRAINT pk_inventory PRIMARY KEY (product_id, warehouse_id) , CONSTRAINT ck_inventory_quantity CHECK (quantity >= 0) ); |
Now three inserts. A valid count, a negative count, and a NULL.
|
1 2 3 |
INSERT INTO dbo.inventory (product_id, warehouse_id, quantity) VALUES (100, 1, 25); /* ok */ INSERT INTO dbo.inventory (product_id, warehouse_id, quantity) VALUES (101, 1, -5); /* rejected */ INSERT INTO dbo.inventory (product_id, warehouse_id, quantity) VALUES (102, 1, NULL); /* ok */ |
The -5 row is rejected, which is what you wanted. The NULL row is accepted, which is not. The constraint you added to stop bad quantities let a row through with no quantity at all.
The cause is the same three-valued logic as the rest of the series, applied by a CHECK constraint instead of a WHERE clause. A CHECK constraint rejects a row only when its predicate evaluates to FALSE. It accepts the row when the predicate is TRUE, and it also accepts the row when the predicate is UNKNOWN. For the NULL insert, quantity >= 0 becomes NULL >= 0, which is UNKNOWN, so the CHECK does not reject it. This is a different acceptance rule from WHERE, which returns a row only when the predicate is TRUE. WHERE drops both FALSE and UNKNOWN, while a CHECK rejects only FALSE.
This is standard CHECK semantics: a constraint is satisfied whenever its condition is not FALSE.[2] So a nullable column with a range CHECK enforces the range for the rows that have a value, and does not prohibit NULL.
There are two ways to get the behavior people usually expect, and which one is right depends on whether a NULL quantity is valid at all. If every inventory row must have a counted quantity, define the column as NOT NULL.
|
1 |
ALTER TABLE dbo.inventory ALTER COLUMN quantity int NOT NULL; |
NOT NULL requires a value independently of the CHECK, so a NULL insert is rejected regardless of the range constraint, and the CHECK is left to enforce the range on the values that are present. If the table already holds NULL rows, this ALTER fails until you update or remove them, which is the schema telling you the same thing the CHECK never did.
If the column has to stay nullable, but a NULL should not satisfy this particular rule, put the NULL case into the constraint.
|
1 2 3 4 |
ALTER TABLE dbo.inventory DROP CONSTRAINT ck_inventory_quantity; ALTER TABLE dbo.inventory ADD CONSTRAINT ck_inventory_quantity CHECK (quantity IS NOT NULL AND quantity >= 0); |
Now the NULL insert makes the predicate NULL IS NOT NULL AND ..., which is FALSE AND UNKNOWN, which is FALSE, and the row is rejected. A present quantity of 25 gives TRUE AND TRUE, which is TRUE, and passes. Choose the definition that matches whether NULL is valid for the column, rather than leaving it to the range CHECK, which accepts NULL on its own.
One more place this shows up is a multi-column CHECK.[3] CHECK (quantity <= capacity) allows a row where either column is NULL, because a comparison with a NULL operand is UNKNOWN and the CHECK permits UNKNOWN. If both values are required for the rule to mean anything, spell out quantity IS NOT NULL AND capacity IS NOT NULL AND quantity <= capacity, or make the columns NOT NULL where that is the real requirement.
The reason this one is easy to miss is that a CHECK constraint reads like a guarantee. The name says check, the predicate says quantity >= 0, and every test with a non-NULL value behaves. The gap only shows when a NULL arrives, and by then the row is already stored.
Next up is Part 5, what NULL does to your aggregates, where COUNT(*), COUNT(column), COUNT(DISTINCT column), and AVG(column) give different answers once a column holds NULL.
If a CHECK constraint has ever let something past that you assumed it would stop, I would like to hear about it. You can find me on Bluesky and LinkedIn.
References
- Unique Constraints and Check Constraints - Microsoft Learn. Documents CHECK constraint behavior, including that a row is rejected only when the condition evaluates to FALSE. ↩
- NULL Complexities, Part 2 - SQLPerformance, Itzik Ben-Gan. Shows that a CHECK constraint accepts TRUE and UNKNOWN and rejects only FALSE, so a nullable column still allows NULL. ↩
- Create Check Constraints - Microsoft Learn. Covers defining CHECK constraints, including multi-column expressions. ↩