Support for CHECK constraints

[luxint]

Hi,

I noticed the transform.table() method doesn't have an option to add/change or drop a check constraint (see https://sqlite.org/lang_createtable.html -> 3.7 Check Constraints. would be great to have this as an option!

[simonw]

This goes beyond the transform() method - the curious methods that create new SQL tables could benefit from the ability to add CHECK constraints too.

I haven't used these myself, do you have any CREATE TABLE examples that use them that you can share?

[luxint]

This goes beyond the transform() method - the curious methods that create new SQL tables could benefit from the ability to add CHECK constraints too.

I haven't used these myself, do you have any CREATE TABLE examples that use them that you can share?

I'm using them myself for the first time as well, this is a tutorial of how to use (and change) them in sqlite: https://www.sqlitetutorial.net/sqlite-check-constraint/

[simonw]

I've been thinking about this more recently.

I think the first place to explore these will be in the create-table command (and underlying APIs).

Relevant docs: https://www.sqlite.org/lang_createtable.html#check_constraints

A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. 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. The expression of a CHECK constraint may not contain a subquery.

Something like this:

sqlite-utils create-table data.db entries id integer title text tags text --pk id --check tags:json

Where --check tags:json uses a pre-baked recipe for using the SQLite JSON function to check that the content is valid JSON and reject it otherwise.

Then can bundle a bunch of other pre-baked recipes, but also support the following:

--check 'x > 3' --check 'length(phone) >= 10'

The besign reason for the column:recipe format here is to reuse --check for both pre-defined recipes that affect a single column AND for freeform expressions that get added to the end of the table.

Detecting column name:recipe with a regex feels safe to me.

[simonw]

Quick demo of a check constraint for JSON validation:

sqlite> create table test (id integer primary key, tags text, check (json(tags) is not null));
sqlite> sqlite> insert into test (tags ('["one", "two"]');
sqlite> insert into test (tags) values ('["one", "two"');
Error: stepping, malformed JSON (1)

[simonw]

A few other recipes off the top of my head:

• title:maxlength:20 - set a max length, length(title) <= 20
• created:date - check for yyyy-mm-dd date, select :date == date(:date) is not null (demo)
• age:positiveint - check age is a positive integer, printf('%', age) = age and age > 0 (untested)

[simonw]

Make me think also that sqlite-utils create-table should have an option to dump out the SQL without actually creating the table.

[luxint]

HI Simon,

looks good, I noticed you wanted to use a regex to detect it, you might be interested in github.com/iafisher/sqliteparser which creates an ast of the create table statement, not every option supported yet but i forked it and am adding all the possible options in a create table (and create index) statement.