Is it possible to enforce SQLite datatypes?

I believe you could use a CHECK constraint e.g. 

CREATE TABLE testinsert (
    COL1 INTEGER, 
    COL2 INTEGER CHECK (CAST(COL2||1 AS INTEGER) <> 0) -- column constraint
    CHECK (CAST(COL1||1 AS INTEGER) <> 0) -- table constraint
);
  • this creates a table with two columns COL1 and COL2.

  • both columns have the same constraint but applied to the columns differently

    • COL1 has the CHECK constraint applied as table-constraint
    • COL2 has the CHECK constraint applied as a column-constraint

  • The constraint works by checking the value of the column when CAST to an INTEGER. The result will be 0 (false) if the value is not an INTEGER. However, to cater for 0 as a valid value 1 is concatenated to the column's value. Thus 0 will result in 1.

You could also use the TYPEOF function e.g. 

COL3 INTEGER CHECK(typeof(COL3) = 'INTEGER')
  • Noting that there is a subtle difference between using CAST and TYPEOF. The previous examples, using CAST, will allow integers provided as strings, whilst using the typeof function will only allow integers.

The following are some example INSERTS with the result :-

INSERT INTO testinsert VALUES(100,100); -- OK
INSERT INTO testinsert VALUES('100',100); -- OK
INSERT INTO testinsert VALUES('100','abc'); -- ouch for COL2 (abc)
INSERT INTO testinsert VALUES(0,100); -- OK
INSERT INTO testinsert VALUES('0',100); -- OK
INSERT INTO testinsert VALUES('abc',100); -- ouch  for COL1 (abc)

With the COL3 added to the table :-

INSERT INTO testinsert VALUES(100,100,100); -- OK (all integers)
INSERT INTO testinsert VALUES('100','100',100); -- OK (CAST can accept integers as strings)
INSERT INTO testinsert VALUES('100','100','100'); -- Ouch typeof will consider '100' as a string, not an integer

If you wanted to ignore rather than abort (the default) then you could use :-

INSERT OR IGNORE INTO testinsert VALUES('100','abc'); -- insert Skipped - no fail

SQLite Manager was used for testing the above.

Here's an example of the CONFLICT failure :-

SQLiteManager:  -- OK
INSERT INTO testinsert VALUES('100','abc'); [ CHECK constraint failed: testinsert ]
Exception Name: NS_ERROR_STORAGE_CONSTRAINT
Exception Message: Component returned failure code: 0x80630003 (NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]

You may wish to have a look at :-

  • CREATE TABLE
  • table-constraint
  • column-constraint
  • ON CONFLICT clause
  • expression - CAST expressions (near the bottom)
  • typeof(X)

You can use CHECK constraints and typeof() to check the actual data type:

CREATE TABLE MyTable (
    Col1 INTEGER  CHECK (typeof(Col1) = 'integer'),
    Col2 TEXT     CHECK (typeof(Col2) IN ('text', 'null')
);

Tags:

Sqlite

Recent Posts