sqlite Enforcing column types


Example

SQLite uses dynamic typing and ignores declared column types:

> CREATE TABLE Test (
      Col1 INTEGER,
      Col2 VARCHAR(2),       -- length is ignored, too
      Col3 BLOB,
      Col4,                  -- no type required
      Col5 FLUFFY BUNNIES    -- use whatever you want
  );
> INSERT INTO Test VALUES (1, 1, 1, 1, 1);
> INSERT INTO Test VALUES ('xxx', 'xxx', 'xxx', 'xxx', 'xxx');
> SELECT * FROM Test;
1   1   1   1   1         
xxx xxx xxx xxx xxx       

(However, declared column types are used for type affinity.)

To enforce types, you have to add a constraint with the typeof() function:

CREATE TABLE Tab (
    Col1 TEXT   CHECK (typeof(Col1) = 'text' AND length(Col1) <= 10),
    [...]
);

(If such a column should be NULLable, you have to explicitly allow 'null'.)