How flexible/restricive are SQLite column types?

by

SQLite column types are flexible (dynamic), primarily, it appears to cater for the adoption/adaptation of rigid column types used by other database Management Systems.

Note! this Asnwer is NOT recommending use of weird and wonderful column types.

1) You can actually use virtually any name for a column type, there are however some limitations.

2) Column type is the 2nd value in the column definition e.g. CREATE TABLE table (columnname columntype .....,....), although it may be omitted intentionally or perhaps inadvertently Note see 5a)

3) The first limitation is that mycolumnINTEGER PRIMARY KEY or mycolumnINTEGER PRIMARY KEY AUTOINCREMENT is a special column type. The column is an alias for the rowid which is a unique numeric identifier (AUTOINCREMENT imposes a rule that the rowid must be greater than the last used rowid for the table e.g. if a row uses id (9223372036854775807), then any subsequent attempts to add a row will result in an SQLITE FULL error. ). SQLite Autoincrement

4) Other limitations are that the column type mustn’t confuse the SQLite parser. For example a column type of PRIMARY, TABLE, INDEX will result in an SQLite exception (syntax error (code 1)) e.g. when a column type of INDEX is used then:-

android.database.sqlite.SQLiteException: near "INDEX": syntax error (code 1):

occurs.

5) A column type is not mandatory, for example CREATE TABLE mytable (...,PRIMARY_COL,.... in which case a PRAGMA TABLE_INFO(tablename) will show no type e.g. (3rd Line).

08-08 07:56:23.391 13097-13097/? D/TBL_INFO: Col=cid Value=8
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=name Value=PRIMARY_COL
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=type Value=
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=notnull Value=1
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=dflt_value Value=null
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=pk Value=0

5a) In some cases the SQLite Parser will skip to valid KEYWORDS e.g. CREATE TABLE mytable (mycolumn NOT NULL,... results in NOT NULL being used to indicate a NOT NULL column and the type being taken as no type (the table_info above was actually from such a usage).

6) A type is not limited to a single word e.g. VARYING CHARACTER(255) or THE BIG BAD WOLF can be specified as a type as can be seen from this table_info extract :-

08-08 08:23:26.423 4799-4799/? D/   TBLINFO: Col=type Value=THE BIG BAD WOLF

The reason to use non-standard column types in SQLite!

In short there is no reason, as stated at first, the flexibility of column types appears to be primarily to cater for the easy adaptation of SQL from other Database Management Systems.

Column types themselves have little effect as data will be stored according to the what SQLite determines as the storage class to be used. With the exception of rowid (see 3) above) any column can hold values of any type.

With the exception of data stored as a Blob, which must be retrieved using the cursor.getBlob and that cursor.getBlob cannot be used for data not stored as a BLOB (getBlob doesn’t fail with data stored as TEXT), You can very much retrieve data (all be it not necessarily useful) using any of the cursor.get???? methods.

Here’s some examples:-

For a column where the data long myINT = 556677888; is added (via ContentValues e.g. cv1.put(columnanme,myINT));

Then :-

08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=INTEGER_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>5.56677888E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>5.566779E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>15104<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:      Unable to handle with getBlob.

getShort does not return to the stored value, getBlob cannot get the stored value.

For Double myREAL = 213456789.4528791134567890109643534276; :-

08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Column=REAL_COL<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>6037<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:      Unable to handle with getBlob.

For String myTEXT = "The Lazy Quick Brown Fox Jumped Over the Fence or something like that.";

08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=TEXT_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>The Lazy Quick Brown Fox Jumped Over the Fence or something like that.<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS BLOB >>[B@2f9e811e<<

And here’s a pretty ridiculous example with a column type of my_char_is_not_a_char_but_an_int as per PRAGMA TABLE_INFO :-

08-08 09:19:03.657 13575-13575/mjt.soqanda D/TBL_INFO: Col=cid Value=7
08-08 09:19:03.657 13575-13575/mjt.soqanda D/   TBLINFO: Col=name Value=my_char_is_not_a_char_but_an_int_COL
08-08 09:19:03.657 13575-13575/mjt.soqanda D/   TBLINFO: Col=type Value=my_char_is_not_a_char_but_an_int
08-08 09:19:03.657 13575-13575/mjt.soqanda D/   TBLINFO: Col=notnull Value=0
08-08 09:19:03.657 13575-13575/mjt.soqanda D/   TBLINFO: Col=dflt_value Value=null
08-08 09:19:03.657 13575-13575/mjt.soqanda D/   TBLINFO: Col=pk Value=0

Results (stored as per ‘Double’ above) are:-

08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: Column=my_char_is_not_a_char_but_an_int_COL<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>6037<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes:      Unable to handle with getBlob.

The above was based upon the following:-
Datatypes In SQLite Version 3
SQLite Autoincrement
PRAGMA Statements

Code was tested/run on a GenyMotion emulated device running API22 compiled with a min version of 14 and target of 26.

More Related Contents:

  1. Are there any methods that assist with resolving common SQLite issues?
  2. How to use images in Android SQLite that are larger than the limitations of a CursorWindow?
  3. Need a bit of assistance [closed]
  4. Android pass Fragment parameter instead of List into ListView [closed]
  5. CursorOutofBoundsException Index -1 requested with size of 0
  6. Why does the application crash when I press the button to insert the values in the table? [duplicate]
  7. Save data in a SQLite database with the original format and view those data again with that form format
  8. When does SQLiteOpenHelper onCreate() / onUpgrade() run?
  9. Ship an application with a database
  10. How to access data/data folder in Android device?
  11. View contents of database file in Android Studio
  12. android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
  13. How to use ROW_NUMBER in sqlite
  14. Android SQLite issue – table … has no column named
  15. How to perform an SQLite query within an Android application?
  16. How to use Room Persistence Library with pre-populated database?
  17. Android: column ‘_id’ does not exist
  18. How can I insert image in a sqlite database
  19. How to store and retrieve a byte array (image data) to and from a SQLite database?
  20. GROUP_CONCAT in SQLite
  21. What are the overheads of using AUTOINCREMENT for SQLite on Android?
  22. A pre-populated database does not work at API 28 throws “no such table” exception
  23. SQLite returned an error code of 14
  24. Attempt to reopen an already-closed object sqlitedatabase
  25. How insert image in room persistence library?
  26. How to convert milliseconds to date in SQLite
  27. How to check database on not rooted android device
  28. PowerManager.PARTIAL_WAKE_LOCK android
  29. Will SQLite performance degrade if the database size is greater than 2 gigabytes?
  30. Get the field value with a Cursor

Leave a Comment