SQLite Forum

Check Contraints
Login

Check Contraints

(1) By anonymous on 2020-10-13 02:12:32 [link] [source]

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 [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.

(3) By jake on 2020-10-13 04:57:46 in reply to 2.1 [link] [source]

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

(4) By Keith Medcalf (kmedcalf) on 2020-10-13 05:16:35 in reply to 3 [link] [source]

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.

(5) By anonymous on 2020-10-13 06:48:57 in reply to 4 [link] [source]

Thank you that did help