Regarding check constraint null values

Question

CREATE TABLE A (
   one INT CHECK (one > 0),
   two INT CHECK (two > 0),
   three INT CHECK (three > 0),
   four INT CONSTRAINT ok CHECK (
   four < one + two + three),
   PRIMARY KEY (one, two)
);

I have the following table above. Fields one, two, three must be positive, and field four must be less than the sum of each of these fields.

How come when I insert insert into A values (1, 2, 3, null);, the query does not fail?

It doesn't make sense for null < 1 + 2 + 3, but it still lets me enter it?

@Marth All of the columns must be `NOT NULL` for this to work. If `three` were NULL, the check constraint would still fail. — Miles Elam, Dec 18 '19 at 19:17
@MilesElam: You're correct, of course. Somehow missed that while writing my comment (started with `COALESCE(four < one + two + three, false)` but forgot half the checks when changing it to `AND`). Since your answer is pretty much a better version of my comment I've deleted it. — Marth, Dec 18 '19 at 19:22

Miles Elam · Accepted Answer · 2019-12-18T19:24:17.540

NULL does not act like you think it does. Replace NULL in your mind with "unknown value".

"Is an unknown value less than 1 + 2 + 3?"

By itself, the statement is nonsensical. The answer must logically be, "The answer is unknown." And it is.

NULL < 1 + 2 + 3 is not true. It's not false. Literally "unknown value." It is NULL. CHECK statements on NULL fall through because they only fail on known false values. If you don't want NULLs to spoil the party, make sure NULL can never be entered.

CREATE TABLE A (
   one INT NOT NULL CHECK (one > 0),
   two INT NOT NULL CHECK (two > 0),
   three INT NOT NULL CHECK (three > 0),
   four INT NOT NULL CONSTRAINT ok CHECK (four < one + two + three),
   PRIMARY KEY (one, two)
);

Regarding check constraint null values

1 Answers1