Hi, I need to create a check constraint that only allows uppercase letters and numbers between 0 to 9 to be entered.
below is the schema.
CREATE TABLE Vehicle( VIN TEXT NOT NULL, odometer INTEGER NOT NULL, PRIMARY KEY (VIN) CHECK (VIN NOT LIKE '%[A-Z0-9]%') );
(2.1) By Keith Medcalf (kmedcalf) on 2020-10-13 04:19:58 edited from 2.0 in reply to 1 [link] [source]
You could, as one example, compile-in or load the REGEXP extension found in the ext\misc directory https://www.sqlite.org/src/dir?ci=tip&name=ext/misc and use the following CREATE TABLE statement:
create table Vehicle ( Vin text not null primary key, Odometer integer not null, check (Vin regexp '^[A-Z0-9]+$') );
LIKE (or GLOB) will not work since the wildcard (%, _, *, ?) match any character so the LIKE expression that you gave will match any sequence of characters that contains one upper-case letter or number within it (assuming you have set case sensitive like).
The regular expression above will match a string entirely composed only of one or more uppercase letters or numbers.
An uglier way without any extension function dependencies might look something like this:
sqlite> CREATE TABLE Vehicle( ...> Vin TEXT NOT NULL PRIMARY KEY, ...> Odometer INTEGER NOT NULL, ...> CHECK (Vin GLOB Replace(Hex(ZeroBlob(Length(Vin))), '00', '[A-Z0-9]')) ...> ); sqlite> INSERT INTO Vehicle VALUES ('ABC123', 0); sqlite> INSERT INTO Vehicle VALUES ('ABCx123', 0); Error: CHECK constraint failed: Vin GLOB Replace(Hex(ZeroBlob(Length(Vin))), '00', '[A-Z0-9]')
You would also need to check that length(vin) > 0.
CREATE TABLE Vehicle( Vin TEXT NOT NULL PRIMARY KEY, Odometer INTEGER NOT NULL, CHECK (length(vin) > 0 and Vin GLOB Replace(Hex(ZeroBlob(Length(Vin))), '00', '[A-Z0-9]')) );
That is a very neat idea though.