SQLite is incredible but it is sometimes quirky and the new GENERATED ALWAYS syntax unexpectedly disappointed me when I read about it with the 3.31.0 release.

SQLite is incredible but it is sometimes quirky and the new GENERATED ALWAYS syntax unexpectedly disappointed me when I read about it with the 3.31.0 release. GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS is IBM DB2 syntax that became part of the SQL standard. It is meant to be the unifying syntax for all the various flavors of AUTOINCREMENT that exist (GENERATED ALWAYS AS IDENTITY is now the standard way). It also makes a distinction between ALWAYS generated and BY DEFAULT which can be bypassed with a user supplied value. SQLITE's GENERATED ALWAYS doesn't apply to keys/indexes so it doesn't work in the main use case in the SQL standard; primary key generation. Hopefully the AS IDENTITY syntax will eventually be added to SQLite to fix the goofy INT PRIMARY KEY vs. INTEGER PRIMARY KEY (long-form INTEGER is a synonym for IDENTITY/AUTOINCREMENT) syntax. The JSON Document use case is useful but nowhere near as useful as supporting a clean and standard GENERATED ALWAYS AS IDENTITY for primary keys, in my opinion. Maybe I'm missing something.