sqliteconstraintssql-likecreate-tablecheck-constraints

CHECK constraint matching beginning of value

We are using sqlite3 for a database and ensuring values inserted into a column matching a specific string would be useful in our case.

Example:

CREATE TABLE people ("firstname" TEXT),
CHECK(LIKE('ca%',"firstname"));

The error we see is that sqlite3 gives this error:

Parse error: near "CHECK": syntax error
  CREATE TABLE people ("firstname" TEXT), CHECK(LIKE('ca%',"firstname"));
                            error here ---^

We want this be ok:

INSERT INTO people VALUES ('Callie');

but this be not ok:

INSERT INTO people VALUES ('Erik');

Is a CHECK with this possible in sqlite3? there is LIKE but everybody seem to only mention SELECT statements.

Solution

  • This is the correct syntax for the constraint:

    CREATE TABLE people (
  firstname TEXT,
  CHECK (firstname LIKE 'ca%')
);

    See the demo.