SQLite, ASCII A-Z Check Constraint

Question

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

• urbanSoft: There's a sqlite extension repsositoriy which contains a regexp extension which should compile under Windows and Linux.

Answer 1

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:

• SQLite Like: Querying Data Based On Pattern Matching
• The LIKE, GLOB, REGEXP, and MATCH operators

Answer 2

LIKE does not support character classes, but the (non-standard) GLOB does:

CHECK (prefix NOT GLOB '*[^a-zA-Z]*')

Answer 3

DB Browser for SQLite has support for the REGEX function (enabled by default) and you can add the function to your python project as the following

def regex(expr, item):
 reg = re.compile(expr)
 return reg.search(item) is not None
conn = sqlite3.connect(':MEMORY:')
conn.create_function("REGEXP", 2, regex)

Here is the (almost) equivalent check using GLOB

CHECK(prefix NOT GLOB '*[^a-zA-Z]*'))

The REGEX check will not allow an empty string, but this will, so if you care you can use

CHECK(length(prefix) > 0 AND prefix NOT GLOB '*[^a-zA-Z]*'))