3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vinayak Agarwal
  • 1,350
  • 3
  • 15
  • 28
  • 2
    Works just fine for me: http://www.ideone.com/ERbhM – mellamokb Nov 16 '11 at 04:52
  • I will. But I guess I have a follow up. I had oversimplified the problem for the sake of asking here. The problem still persists because I am doing something like "create table T(A real check(A is null or A>B), B real)" and maybe because I am accessing B before it is declared, it gives me constraint errors. Any hack for this? – Vinayak Agarwal Nov 16 '11 at 05:06
  • Solved it. Thanks for your help. – Vinayak Agarwal Nov 16 '11 at 05:19

2 Answers2

4

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))
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • If I have "create table T(A real check(A>3));", will it take care of both cases? Wont it constrain the null values because then when A is null then null cannot be greater than 3 and hence the check would fail. – Vinayak Agarwal Nov 16 '11 at 04:54
  • @VinayakAgarwal: Is it really that hard to take 5 minutes to write up some `INSERT` statements and try it out? We value personal effort here too whenever it's possible. – mellamokb Nov 16 '11 at 05:33
4

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138