Michael and Jay are right about the subtleties on how SQlite interprets what is a data type, a primary key and a database schema and it's ACID implementation in general.
To me, the main reason - and remedy - of this FAQ is that SQlite should'nt be called a "SQL database" (as e.g. touted on its homepage). Instead it should clearly declare itself as an "In-memory SQL Datastore" or a "Data container with SQL capabilities". Yours, S. 2011/4/17 Black, Michael (IS) <[email protected]>: > Seems to behave OK for me on 3.7.5 on Windows. What version are you using on > what OS with what compile flags? > > You also "said" it didn't work but you didnt' actually what what you did. > > Like this... > > > > SQLite version 3.7.5 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT, > ...> OtherColumn INTEGER); > sqlite> > sqlite> INSERT INTO Tg (TgConfigId) VALUES (1); > sqlite> SELECT * FROM Tg WHERE TgConfigId = 1; > 1| > sqlite> > sqlite> SELECT * FROM Tg; > 1| > sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1'); > Error: PRIMARY KEY must be unique > sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2'); > sqlite> SELECT * FROM Tg; > 1| > 2| > sqlite> SELECT * FROM Tg WHERE TgConfigId = 2; > 2| > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > ________________________________ > From: [email protected] [[email protected]] on > behalf of Tobias Vesterlund [[email protected]] > Sent: Saturday, April 16, 2011 12:40 PM > To: [email protected] > Subject: EXT :[sqlite] 'integer' > > Hi, > > I ran into something I don't understand, maybe someone here can shed some > light on it for me. > > I have a table named Tg which is created (with tcl) by: > > CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT, > OtherColumn INTEGER); > > If I do: > INSERT INTO Tg (TgConfigId) VALUES (1); > > The following select works: > SELECT * FROM Tg WHERE TgConfigId = 1; > > But if I insert '1' instead I have to select on '1', 1 no longer works. That > makes some sense, but not entirely, as what I inserted isn't an integer any > longer but a string. Why would I be allowed to insert values with '' in a > column that is specified to be an integer? > > Regards, > Tobias > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
