3

I have a problem with the SQLite CHECk Constraint.

SQLite doesn't check if a inserted value is of a specific type (for example integer).

So i want to check this by the "CHECK" Constraint using the "typeof" function.

But when I execute the following query(s), I get no error and SQLite INSERT the string without a message.

What's wrong?

sqlite> CREATE TABLE test2 (test INTEGER CHECK(TYPEOF(test) == 'INTEGER'));
sqlite> INSERT INTO test2 (test) VALUES (5); //Should work
sqlite> INSERT INTO test2 (test) VALUES ('dd'); //Should not work, but works
Maarkoize
  • 2,601
  • 2
  • 16
  • 34
  • Is there an issue using a "=="? I'm a total SQL newb, but at least on [this page](http://stackoverflow.com/questions/2761563/sqlite-data-types) I see they're only using one equal sign. – Dan Goodspeed Oct 16 '13 at 07:11
  • I tried it also with a single =...with no effect – Maarkoize Oct 16 '13 at 07:44
  • 3
    ["Prior to version 3.3.0, CHECK constraints were parsed but not enforced."](http://www.sqlite.org/lang_createtable.html) Is your SQLite version >= `3.3`? – LS_ᴅᴇᴠ Oct 16 '13 at 09:45
  • @DanGoodspeed SQLite makes no distinction between `=` or `==` (see [Operators](http://www.sqlite.org/lang_expr.html)). – LS_ᴅᴇᴠ Oct 16 '13 at 09:49
  • 1
    @LS_dev That was the problem, thanks. The installed version was 3.1, in 3.3 it works, but the INTEGER in '' must be written in small letters. – Maarkoize Oct 16 '13 at 09:59
  • @LS_dev Why not set that as an answer? – CJBS Sep 08 '14 at 23:46
  • Can you try `SELECT TYPEOF(test2) test2` for your both lines, to see what types are deliverd ? – Radon8472 Sep 30 '16 at 12:52

1 Answers1

1

I tried your Queries using NavicatLite on a SQLite3 database and it show

19 - constraint failed

for the text insert, So it seems to be correct.

Please try

select sqlite_version();

and check if it shows something above version 3.3 (LS_dev thank you for your comment)

Radon8472
  • 4,285
  • 1
  • 33
  • 41