There is one limitation to this approach, however. ?The entry for an
autoincrement column in the sqlite_sequence table isn't made until the first
row is inserted into the table. ?If you are also using the C interface, you can
identify autoincrement columns using?sqlite3_table_column_metadata.
Peter
On Wednesday, April 13, 2016 9:40 AM, Igor Korot <ikorot01 at gmail.com>
wrote:
Hi, Kees,
On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt <k.nuyt at zonnet.nl> wrote:
> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
> <ikorot01 at gmail.com> wrote:
>
>> Hi,,
>>
>>On Wed, Apr 13, 2016 at 10:54 AM, J Decker <d3ck0r at gmail.com> wrote:
>>> Yes, you can get the create statement from sqlite_master table
>>
>> I was kind of hoping for a simpler solution so that not to
>> parse "CREATE TABLE" statement...
>>
>> Well, I guess I will have to.
>
> With AUTOINCREMENT, the last autoincremented primary key value
> is tracked in a table called sqlite_sequence.
>
> $ sqlite3 test2.db
> SQLite version 3.8.12 2015-10-07 00:35:18
> Enter ".help" for usage hints.
> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> tx TEXT);
> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
> sqlite> SELECT name FROM sqlite_master WHERE type='table';
> t1
> t2
> sqlite_sequence
> sqlite> SELECT * FROM sqlite_sequence;
> t2|2
> sqlite>
So I can actually query sqlite_sequence table with the table name in WHERE
clause and it should give me the field that is set to be autoincrement.
For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).
Thank you.
P.S.: There is a small typo on the page/paragraph I referenced:
[quote]
The schema for the sqlite_sequence table is:
CREATE TABLE sqlite_sequence(name,seq);
There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT. The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the
largest INTEGER PRIMARY KEY ever used by that table is in the
sqlite_sequence.seq field.
[/quote]
That should be changed to:
[changes}
The name of the table (as it appears in sqlite_master.name) is in the
sqlite_sequence._name_ field
[/changes]
Could someone please correct that? The changes are between the underscores.
Thank you.
>
> Hope this helps
>
>
>>>
>>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot <ikorot01 at gmail.com> wrote:
>>>> Hi,
>>>> Is it possible to get whether the column is set to autoincrement or not?
>>>>
>>>> PRAGMA table_info() does not give such info...
>>>>
>>>> Thank you.
>
> --
> Regards,
> Kees Nuyt
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
