CODEWITHSUNDEEP
sqlite
akai
akai

Reputation: 2052

Is it possible to enforce SQLite datatypes?

From my understanding, sqlite's datatype isn't associated to its column but to the data themselves: this practically means that you can insert any data to any column.

Is it possible to prohibit such a behaviour? I mean, I want sqlite to raise an error (or at least a warning) when I accidentally try to insert text to integer, for example.

Upvotes: 18

Views: 3510

Answers (3)

forpas
forpas

Reputation: 164214

Since version 3.37.0 of SQLite it is possible to force data type checking with STRICT Tables:

In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table.

You can use one of the following data types for the column definitions:

  • INT
  • INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

See a simplified demo.

Upvotes: 4

MikeT
MikeT

Reputation: 57083

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 :-

Upvotes: 6

CL.
CL.

Reputation: 180280

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

Upvotes: 17

Related Questions