3

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

4

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:

2

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

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

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]*'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.