sqlite is pretty loose about types. The column definitions don't constrain
what is stored in the rows at all:
sqlite> CREATE TABLE a(c INTEGER);
sqlite> INSERT INTO a VALUES ("fourty-two");
sqlite> SELECT * FROM a;
fourty-two
So "UNSIGNED" seems kind of pointless as it's implies a further constraint
which is not going to be honoured. Note that sqlite does support actual
constraints via the CHECK clause:
sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
sqlite> INSERT INTO b VALUES (-15);
Error: constraint failed
sqlite> INSERT INTO b VALUES (15);
Although this is still allowed:
sqlite> INSERT INTO b VALUES ("twenty");
sqlite> SELECT * FROM b;
15
twenty
You can disallow it if you get even more specific:
sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
0));
sqlite> INSERT INTO b2 VALUES ("twenty");
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (0);
sqlite> INSERT INTO b2 VALUES (-1);
Error: constraint failed
sqlite> INSERT INTO b2 VALUES (1);
sqlite> SELECT * FROM b2;
0
1
Note that the type in the column definition does have an effect - it
defines the column's "affinity" and may change the way data is stored. For
example:
sqlite> INSERT INTO b2 VALUES ("2");
Error: constraint failed
The TYPEOF check rejects this, but without that constraint:
sqlite> INSERT INTO b VALUES ("2");
sqlite> SELECT c, TYPEOF(c) FROM b;
15|integer
twenty|text
2|integer
ie. the text data we tried to insert was converted to an integer for
storage.
Further reading: https://www.sqlite.org/datatype3.html
-Rowan
On 21 August 2018 at 14:46, D Burgess <dburges...@gmail.com> wrote:
> Is there a historical reason why sqlite does not have a UNSIGNED type
> to go with INTEGER?
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
