SQLite, ASCII A-Z Check Constraint

check-constraintsregexregular expressionsqlite

I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why is it not working?

CREATE TABLE test
(
    id INTEGER NOT NULL PRIMARY KEY, 
    prefix TEXT NOT NULL, 
    CHECK(prefix NOT LIKE '%[^a-zA-Z]%')
)

Using Python's sqlite3 package and DB Browser for SQLite

Best Answer

The 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: % means (0 or more of anything), which would be the equivalent of a .* in a regexp, and _ meaning (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 [a-zA-Z].

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:

Related Question