That is correct. You cannot spell "INTEGER PRIMARY KEY", which is an explicit alias for the rowid, as anything other than that one particular correct spelling.
Declaring "bigint primary key" (ie, using an incorrect spelling) defines a column that IS NOT an explicit alias for the rowid. It is merely an integer column, completely separate from the rowid, whcih you happen to want to be the primary key. Since the AUTOINCREMENT only applies to the rowid, you must spell the declaration correctly if you wish to (a) alias the rowid and (b) apply the AUTOINCREMENT option to the rowid. And no, "BIGINT PRIMARY KEY" is not an alias for "INTEGER PRIMARY KEY" https://sqlite.org/autoinc.html https://sqlite.org/datatype3.html https://www.sqlite.org/lang_createtable.html see "ROWIDs and the INTEGER PRIMARY KEY" Personally, I have never found a use for the AUTOINCREMENT option. Why is it being used? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Peter Halasz >Sent: Thursday, 23 November, 2017 17:14 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] "BIGINT" doesn't act like INTEGER on table creation >[Bug] > >BIGINT has a "resulting affinity" of INTEGER ( >https://sqlite.org/datatype3.html) but cannot be used in its place in >the >following example: > > CREATE TABLE [FailTable] ( [id] BigInt PRIMARY KEY AUTOINCREMENT >) > >...as it gives this error: > > AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > >Manually replacing BIGINT with INTEGER leads to success: > > CREATE TABLE [OkTable] ( [id] Integer PRIMARY KEY AUTOINCREMENT ) > >I believe BigInt should work here the same as Integer, as it does in >other >contexts. I feel like this is a bug that could be fixed in SQLite. Or >otherwise you might wish to mention the odd exception of >AUTOINCREMENT >fields explicitly in the type documentation -- but it seems to me >like this >is more something that should be fixed in the code. > >Sorry if this is a duplicate, known or already patched bug. It was >difficult to search the bug tracker. > >Thanks for taking the time to look into this. > >For background, how I came across this: Linq2db, a library for C#, >generates bad SQL in this style and fails. Clearly that library needs >to >fix its SQL generator to work better with SQLite (and I've posted a >bug >report on their github). However, it also seems odd the SQL should >produce >an error at all. If BigInt and Integer were treated synonymously, as >documented, It would be valid. So I felt the bug should be brought to >the >attention of the SQLite developers. Hopefully it is something that >can be >fixed. > >Cheers >_______________________________________________ >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