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

Reply via email to