CODEWITHSUNDEEP
sqlsqlitedbnullcheck-constraints
Vinayak Agarwal
Vinayak Agarwal

Reputation: 1410

Check Constraints in SQL- Specify a value could be null or a constraint

I am trying to incorporate a check constraint in SQLite where the requirement is the following:

  1. The value could be null
  2. If the value is not null then it should be greater than 3.

So, in my create table I wrote

create table T(A real check(A = null or A>3));

For this it looks like it creates the table, but it does not enforce the greater than condition.

And I also tried

create table T(A real check(A is null or A>3));

This does not even create the table in the first place.

Can somebody please help on how to enforce both of these constraints?

Thanks

Upvotes: 3

Views: 1449

Answers (2)

onedaywhen
onedaywhen

Reputation: 57093

From the SQL-92 Standard:

A table check constraint is satisfied if and only if the specified search condition is not false for any row of a table.

If A is null then A > 3 will, thanks to SQL's three-valued logic, evaluate to UNKNOWN. Because UNKNOWN is not FALSE the constraint will be satisfied.

Consider that if the Standard was not written that way then every constraint would have to explicitly test for null, which would be more 'noise' and pain for coders.

Upvotes: 4

Sparky
Sparky

Reputation: 15115

Why do you need both? Simply allow the field to be NULL and make the condition A>3. The field will either be NULL or if NOT NULL, greater than 3...

CREATE TABLE (A real NULL check(A>3))

Upvotes: 4

Related Questions