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 [link] [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))
(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.
(4.1) By Keith Medcalf (kmedcalf) on 2020-12-25 19:34:01 edited from 4.0 in reply to 1 [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