I see - thanks Paul. I misunderstood. Thanks for your detailed
explanation.
Chris
On Wed, Mar 21, 2018 at 9:13 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
