Actually it is totally different Chris
> I read that - but my point was more that some people seem to think that
an
> int primary key can be auto incrementing, it can't.
an INT primary key cannot be autoincrementing
An INTEGER primary key and an INTEGER primary key autoincrement work in
essentially the same way. i.e. if you insert a row and do not specifically
assign a value to the pk (i.e. you assign NULL) the value assigned will
usually be one more than last pk used.
if you have an INT primary key and add a new row with no value assigned to
the PK then null will be stored (all null values are treated as unique in
SQLite and so as far as the PK is concerned all rows are different).
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id int primary key, data text);
sqlite> insert into test (data) values('row 1');
sqlite> insert into test (data) values('row 2');
sqlite> select id, data from test;
|row 1
|row 2
of course the rowid is still there hidden behind the scenes and you can
access it with
sqlite> select rowid, id, data from test;
1| |row 1
2| |row 2
but if you want to use the rowid as the PK then you should probably use an
INTEGER pk so it becomes an alias for the rowid in the first place.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
On 20 March 2018 at 16:44, Chris Locke <sql...@chrisjlocke.co.uk> wrote:
> > some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way .... sort of. Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again. Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
> On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> > I read that - but my point was more that some people seem to think that
> an
> > int primary key can be auto incrementing, it can't.
> >
> >
> > SQLite version 3.18.0 2017-03-28 18:48:43
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table test (id integer primary key autoincrement);
> > sqlite> create table test2 (id int primary key autoincrement);
> > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > sqlite>
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:
> >
> > >
> > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > >
> > >> Autoincrement can ONLY be used with an integer primary key
> > >>
> > >
> > > I think Peter's shouting is more about the inability to distinguish via
> > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> both
> > > of which are of course integer and can be auto-incrementing, but only
> one
> > > of which is an alias for rowid.
> > >
> > >
> > >
> > > _______________________________________________
> > > 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
