sqlite Data types 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'.)
