Search code examples
sqlsqlitedbnullcheck-constraints

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

Solution

  • 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))