use sqlite check to validate whether date with proper format is entered in the column

Question

I have created a table as below:

CREATE TABLE case_status(data_entry_timestamp DATETIME DEFAULT (datetime('now','localtime')) NOT NULL,
                         case_number TEXT PRIMARY KEY NOT NULL, 
                         case_name TEXT DEFAULT MISSING, 
                         death_reportdate DATE CONSTRAINT death_reportdate_chk CHECK (death_reportdate==strftime('%Y-%m-%d',death_reportdate)),
                         );

The column death_reportdate need to have a date with pre-defined format (e.g. 2000-12-31). I created the table, inserted some rows of data, and then try to modified data in death_reportdate, the check rule seems to be bypassed when I enter some random string to it.

What have I done wrong?

Answer 1

You had an extra comma at the end. Correct code:

CREATE TABLE case_status(data_entry_timestamp DATETIME DEFAULT (datetime('now','localtime')) NOT NULL,
    case_number TEXT PRIMARY KEY NOT NULL, 
    case_name TEXT DEFAULT MISSING, 
    death_reportdate DATE CONSTRAINT death_reportdate_chk CHECK (death_reportdate==strftime('%Y-%m-%d',death_reportdate))
    )

Answer 2

it is an old Topic but i had the the same Problem. if the strftime method Fails to Format the string( a bad Input) it retuns null, so you have to check is not null in the end

Answer 3

Here is another solution which works like a charm:

`date` DATE CHECK(date IS strftime('%Y-%m-%d', date))

This also works with the time:

`time` TIME CHECK(time IS strftime('%H:%M:%S', time))

Use this to define your column. I think that is a more elegant solution than checking for null value.

Answer 4

First, two small notes.

1. I'm using the TEXT type since SQLite does not have "real types." It has 5 column "affinities", INTEGER, TEXT, BLOB, REAL, and NUMERIC. If you say DATE then it uses NUMERIC which can behave a little weirdly in my opinion. I find it best to explicitly use one of the 5 affinities.
2. I'm using date(...) instead of strftime('%Y-%m-%d', ...) because they are the same thing.

Let's break down why the original question did not work.

DROP TABLE IF EXISTS TEMP.example;
CREATE TEMPORARY TABLE example (
    deathdate TEXT CHECK (deathdate == date(deathdate))
);
INSERT INTO TEMP.example (deathdate) VALUES ('2020-01-01');
INSERT INTO TEMP.example (deathdate) VALUES ('a');
INSERT INTO TEMP.example (deathdate) VALUES (NULL);
SELECT * FROM TEMP.example;

Running this lets all three values get into the database. Why? Let's check the documentation for CHECK constraints.

If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation.

If you run SELECT 'a' == date('a'); you'll see it is NULL. Why? Check SELECT date('a'); and you'll see it is also NULL. Huh, maybe the documentation for == can help?

Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. [...]

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL.

We need to use IS, not ==, and trying that we see that 'a' no longer gets in.

DROP TABLE IF EXISTS TEMP.example;
CREATE TEMPORARY TABLE example (
    deathdate TEXT CHECK (deathdate IS date(deathdate))
);
INSERT INTO TEMP.example (deathdate) VALUES ('2020-01-01');
INSERT INTO TEMP.example (deathdate) VALUES ('a');
INSERT INTO TEMP.example (deathdate) VALUES (NULL);
SELECT * FROM TEMP.example;

If you don't want NULL to get in, simple change it to deathdate TEXT NOT NULL CHECK (deathdate IS date(deathdate))