On 8 Apr 2018, at 11:54am, Markos <mar...@c2o.pro.br> wrote: > CREATE TABLE foods( > id integer PRIMARY KEY, > type_id integer, > name text ); > > I can insert type_id without single quote: > > INSERT INTO foods (name, type_id) VALUES ('Rice', 16); > > And also with single quote: > > INSERT INTO foods (name, type_id) VALUES ('Bean', '17'); > > select * FROM foods; > > ... > 423 16 Rice > 424 17 Bean > > What are the consequences of inserting values in fields of datatype integer > with single quotes?
Well done for testing what happens rather than just assuming things worked the way you thought. When you created the table you specified the affinity of each column. If you define a column as INTEGER but supply a string, SQLite checks to see whether that string represents an integer. If it does, then it stores the integer, effectively doing the translation for you. The actual value stored is integer, and there is no trace that it was originally specified as a string. A similar thing happens when you specify that a column has affinity of REAL. In both cases, SQLite considers that the CREATE command knows better than whatever specifies the value, and does the conversion. However for the number to be stored the conversion has to be reversible. If SQLite reverses the conversion and doesn't get the original string back it stores the string instead. You can test to see what SQLite did using SELECT id, type_id, typeof(type_id), name FROM foods See section 3 (more usefully, the entire page) of <https://www.sqlite.org/datatype3.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users