0

I don't understand why this query (that is not correct) does not raise any error:

CREATE TABLE test (
  ID, 
  VARIANT riri fifi lili,
  PRIMARY KEY(ID, VARIANT ASC))

Also is their any difference from:

  VARIANT TINYINT
  VARIANT INTEGER
  VARIANT BIGINT
  VARIANT UNSIGNED INTEGER
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
zeus
  • 12,173
  • 9
  • 63
  • 184
  • The answer to both of these questions is clearly explained in the SQLite [docs](http://sqlite.org/datatype3.html). Did you not even _try_ to find the answer to this? – Mark Benningfield Feb 09 '18 at 19:52
  • i didn't find where it's say the purpose to let user write VARIANT riri fifi lili whithout error ? – zeus Feb 09 '18 at 20:11
  • 2
    SQLite's default field type is VARIANT. In fact, it's actually *harder* in SQLite to coerce fields to specific types. I suspect your VARIANT clause (including all of your riri fifi stuff) is simply being ignored. – Robert Harvey Feb 09 '18 at 20:17
  • What's more. If you're using FireDAC, it has its own subset of [supported types](http://docwiki.embarcadero.com/RADStudio/en/Using_SQLite_with_FireDAC#Mapping_SQLite_to_FireDAC_Data_Types). – Victoria Mar 30 '18 at 23:29

1 Answers1

2

SQLite has a restricted set of column types (column affinity) (TEXT, NUMERIC, INTEGER, REAL and BLOB). However, it will accept virtually anything as a column type (see How Flexible/Restrictive are SQLite column Types below) and convert this according to a set of rules to one of the restricted set of column types:-

  • If the column type contains INT then it will convert it to INTEGER.
  • If the column type contains CHAR, CLOB or TEXT then it will convert it to TEXT.
  • If the column type contains BLOB then it will convert it to BLOB.
  • If the column type contains REAL, FLOA or DOUB then it will convert it to REAL.
  • Otherwise it will convert it to NUMERIC.

So riri fifi lili will have a column type of NUMERIC (drops through to the last rule).

TINYINT, INTEGER, BIGINT, UNSIGNED INTEGER will all have a column type of INTEGER (as they all contain INT and meet the 1st rule).

CHARINT will have a column type if INTEGER (it meets the 1st rule).

Saying that a column can in fact contain any type, although the column's type (affinity) can have subtle nuances when retrieving data (see Datatypes in SQLite3 below). Note an exception to this is a column that is an alias of rowid.

A more comprehensive answer can be found here How flexible/restricive are SQLite column types?

You may also wish to check out Datatypes In SQLite Version 3

MikeT
  • 51,415
  • 16
  • 49
  • 68