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
