On Thursday, 2 February, 2017 09:12, Clyde Eisenbeis <cte...@gmail.com> inquired:
> What about the INTEGER PRIMARY KEY (defined in my first email post)? > Does this field start at 1, and then auto increment for each new line? INTEGER PRIMARY KEY declares a field to be an alias for the rowid. If you insert NULL (or do not specify a value) then the value is one greater than the largest rowid currently in use, that is also at least 1. So newrowid = max(1, max(rowid) + 1). If you add the AUTOINCREMENT keyword, then the semantics changes slightly and the max(rowid) ever seen for the table is stored in another table, and that value is incremented by 1 to determine the new rowid. In all cases, the minimum value of an automatically generated rowid is 1. In either case you can explicitly insert (or update) a rowid to contain any 64-bit signed integer value. In the case of a definition with AUTOINCREMENT, the max rowid ever seen is saved, so if you did something like: insert into x (key, data) values (NULL, 'test'); update x set key = 1000 where data='test'; delete from x; insert into x (key, data) values (NULL, 'again'); then if the definition of column key was INTEGER PRIMARY KEY, the end row in x would be (1, 'again'). If it was INTEGER PRIMARY KEY AUTOINCREMENT then the row should be (1001, 'again'). "INTEGER PRIMARY KEY" is special and can only contain integers -- it is an alias for the rowid. Other primary key (eg TEXT PRIMARY KEY) can contain any data type and has the same effect as declaring it TEXT UNIQUE. sqlite> create table x (key integer primary key, value text); sqlite> insert into x values (null, 'test'); sqlite> update x set key=10000 where value='test'; sqlite> select * from x; 10000|test sqlite> delete from x; sqlite> insert into x values (null, 'again'); sqlite> select * from x; 1|again sqlite> drop table x; sqlite> create table x (key integer primary key autoincrement, value text); sqlite> insert into x values (null, 'test'); sqlite> update x set key=10000 where value='test'; sqlite> select * from x; 10000|test sqlite> delete from x; sqlite> insert into x values (null, 'again'); sqlite> select * from x; 2|again sqlite> drop table x; sqlite> create table x (key integer primary key autoincrement, value text); sqlite> insert into x values (null, 'test'); sqlite> update x set key=10000 where value='test'; sqlite> select * from x; 10000|test sqlite> insert into x values (null, 'again'); sqlite> select * from x; 10000|test 10001|again sqlite> > On Thu, Feb 2, 2017 at 5:02 AM, R Smith <rsm...@rsweb.co.za> wrote: > > > > > > On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote: > >> > >> I don't see MEMO listed. I needed to use MEMO in Microsoft Access to > >> handle char strings longer than 256. > >> > >> However, I don't see any complaints by SQLite when I use MEMO. > > > > > > SQLite will never complain about anything you use as a type. What you > should > > to use is TEXT. In SQLite any string value, of any length* can be stored > in > > any column, but will be "handled like text" when the column is of the > TEXT > > type affinity, and also allow a collation method. Honestly, you can put > the > > text of the complete volumes of Don Quixotic in a single TEXT field in a > > database. > > > > * - As long as the string memory doesn't exceed 4GB, the char-size of > which > > may differ based on encoding, and if you add the quoted string to the > query > > text directly (as opposed to binding it via the API), then the maximum > size > > will be governed by the max character length of the query input, which > can > > be configured via the API also. You can add the string as memory bytes > as a > > BLOB to go bigger, but then you lose collation capability.... I'll stop > > there, it's probably more unnecessary info than you wished for already. > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
