LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters:
(0 or more of anything), which would be the equivalent of a
.* in a regexp, and
(1 of anything), which would be the equivalentof
. in a regexp. And that's it.
This means your
CHECK condition, as originally written, is not actually checking what you expect. The only thing it will complain about are statements like:
INSERT INTO test
VALUES (37, 'hello [a-zA-Z] impossible');
that contain the literal
You can actually use a
REGEXP operator and write:
CHECK (prefix REGEXP '^[a-zA-Z]+$')
However, to be able to use it, you first need to install the
sqlite3-pcre pcre regex extension for sqlite3, on a Linux machine (there may be a Windows alternative, but I've not been able to find it). You can find more information and instructions at Stack Overflow's How do I use regex in a SQLite query?.
Other databases such as PostgreSQL would let you use RegExp using the
~ operator. You can check it at SQLFiddle.
SQLite doc references and tutorials: