Funny timing. I just discovered the same about 3 days ago, too. I've been a consumer of SQLite databases but never had much of a need to create one. I was somewhat surprised to say the least. Interestingly, it appears AUTOINCREMENT changes ROWID's behavior [1] to stop ID roll over and reuse.

Apparently you can't use AUTOINCREMENT on tables using WITHOUT ROWID either.

[1] https://www.sqlite.org/autoinc.html