"Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses dynamic typing <https://www.sqlite.org/datatype3.html>. The declared type of a column is used to determine the affinity <https://www.sqlite.org/datatype3.html#affinity> of the column only." https://www.sqlite.org/lang_createtable.html
Each column in an SQLite 3 database is assigned one of the following type affinities: - TEXT - NUMERIC - INTEGER - REAL - NONE ...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 NONE. 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. https://www.sqlite.org/datatype3.html#affinity Thus, in the affinity rules, "Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity." These rules allow complex SQL CREATE TABLE statements to be run (for compatibility) and mapped to the five storage affinities (which keep SQLite small and fast) used by SQLite. Jim Callahan Orlando, FL On Thu, Jul 16, 2015 at 6:09 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 16 Jul 2015, at 10:46pm, Hayden Livingston <halivingston at gmail.com> > wrote: > > > It seems that > > > > CREATE TABLE A( something varchar(255) ) > > > > it just > > > > CREATE TABLE A( something text ) > > > > Why have it at all? > > Lots of people take SQL code originally intended for other SQL engines, > start up SQLite and want it to work first time. So it does. It won't > perfectly copy the behaviour of engines which respect the '255' but it's > good enough to assure the programmer that SQLite is worth using. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
