CODEWITHSUNDEEP
sqlite
DIVAKAR VENKATRAMANI
DIVAKAR VENKATRAMANI

Reputation: 237

Datatype is weird but yet is accepted in sqlite3

I created a table like this

create table tablename (name blahblahblah);

Datatype of name is strange but yet accepted by sqlite3. Why is an error message like "Invalid datatype" not thrown?

Upvotes: 0

Views: 42

Answers (1)

glibdud
glibdud

Reputation: 7840

SQLite treats datatypes differently than most other database systems. They're not associated with columns, but with each individual value. When you specify the "datatype" of a column when you create a table, what you're really doing is defining what SQLite calls a "type affinity":

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

The datatypes documentation describes how affinity is determined:

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

  1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.
  2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
  3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
  4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
  5. Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.

Note that rule 5 is a catch-all. There's therefore no such thing as an "invalid" datatype. In your example, since it doesn't fit any other rules, a column type of "blahblahblah" would have a NUMERIC affinity.

Upvotes: 1

Related Questions