CHECK constraints

[federico-razzoli]

CREATE TABLE t (
    birth_date INT,
    death_date INT,
    [ CONSTRAINT chk_a_b ] CHECK (birth_date < death_date OR death_date IS NULL)
);

The CONSTRAINT part is optional and is useful only to specify a name. Names are useful because they appear in error messages.

[FCO]

::?CLASS.^add-constraint-check: { .birth-date < .death-date OR not .death-date.defined };

Or

::?CLASS.^add-constraint-check: chk_a_b, { .birth-date < .death-date OR not .death-date.defined };

_{Sent with GitHawk}

[FCO]

Should it warn when using a driver of database that do not support CHECK

_{Sent with GitHawk}

[jonathanstowe]

I was going to write "or. like SQLite, accept the check definition but don't actually do the check" only to discover that it actually does, which was somewhat of a pleasant surprise.

[FCO]

SQLite do use it? I’m surprised! :)

_{Sent with GitHawk}

[jonathanstowe]

Apparently https://www.sqlite.org/lang_createtable.html I'm of the habit of just assuming that for most things it will accept the definition but just not do anything with it.

[federico-razzoli]

No actually it works as it should in SQLite. In MySQL prior to 8.0 and in MariaDB prior to 10.3 (or something like that) the CHECKs are not enforced.