SQLite Forum

Check date when not null
Login

Check date when not null

(1) By Cecil (CecilWesterhof) on 2020-12-25 11:38:14 [link] [source]

Part of my table definition is:
    created   TEXT DEFAULT CURRENT_DATE() NOT NULL,
    resolved  TEXT DEFAULT NULL,

    CONSTRAINT created CHECK(created = date(strftime('%s', created), 'unixepoch')),


In this way I know that in created is always a correct date.
When resolved is not null, it has to also be a correct date and beside that it has to be greater as created. How should I create this constraint?

(2) By J. King (jking) on 2020-12-25 13:24:59 in reply to 1 [source]

It sounds like this might have been discussed before?

(3.1) By Keith Medcalf (kmedcalf) on 2020-12-25 18:50:20 edited from 3.0 in reply to 1 [link] [source]

It is a direct translation from the English changing the terms as necessary to be something that can actually be expressed:

CHECK (resolved is null or (resolved == date(resolved, '+0 days') and resolved > created))

(4.1) By Keith Medcalf (kmedcalf) on 2020-12-25 19:34:01 edited from 4.0 in reply to 1 [link] [source]

Note that you mayhaps want:

created text default current_date not null on conflict replace constraint created check (created IS date(created, '+0 days'))

so that if you insert NULL explicitly it becomes the default.

The constraint is changed from == to IS since getting the date of something that is not a date returns NULL (even by your method since the unixepoch of a non-date string is NULL), which will allow an invalid date to be inserted (such as 'crappola') without failing the constraint.

A check constraint is triggered if the boolean expression evaluates to False; it is not triggered if the evaluation result is True or NULL.

This is in contrast to a boolean expression in a WHERE clause where the condition passes only if the result is True; it fails if the evaluation result is False or NULL.

(5) By Keith Medcalf (kmedcalf) on 2020-12-25 19:34:56 in reply to 4.1 [link] [source]

SQLite version 3.35.0 2020-12-23 21:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(created text default current_date not null on conflict replace constraint created check (created == date(created, '+0 days')));
sqlite> insert into x values ('crappola');
sqlite> select * from x;
┌──────────┐
│ created  │
├──────────┤
│ crappola │
└──────────┘
sqlite> drop table x;
sqlite> create table x(created text default current_date not null on conflict replace constraint created check (created IS date(created, '+0 days')));
sqlite> insert into x values ('crappola');
Error: CHECK constraint failed: created

(6) By Keith Medcalf (kmedcalf) on 2020-12-25 19:57:17 in reply to 3.1 [link] [source]

This can be simplified to

CHECK (resolved == date(resolved, '+0 days') and resolved > created)

since if resolved is null then the boolean expression result is NULL, which will not trigger the constraint.