CODEWITHSUNDEEP
sqlite
zeus
zeus

Reputation: 13367

Why this create table SQL work under sqllite3?

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

Upvotes: 0

Views: 71

Answers (1)

MikeT
MikeT

Reputation: 56948

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

Upvotes: 2

Related Questions