When I create a table in sqlDelight, and have `id INTEGER PRIMARY KEY NOT NULL` it seems to auto in...

# squarelibraries

[Colton Idle]

When I create a table in sqlDelight, and have

id INTEGER PRIMARY KEY NOT NULL it seems to auto increment on its own (which is fine), but I would've thought that i had to add AUTOINCREMENT. is that just a sqlite thing, or is sqldelight just doing it on my behalf?

[Derek Ellis]

It's a SQLite thing. an INTEGER PRIMARY KEY column points to the ROWID https://www.sqlite.org/autoinc.html#:~:text=In%20SQLite%2C%20a%20column%20with,a%2064%2Dbit%20signed%20integer.

[vanniktech]

That's also what I would have assumed. It's actually a pretty smart optimization. But only after reading this - https://sqlite.org/queryplanner.html - super interesting read and starts from zero.

[Colton Idle]

I just re-read and noticed this line

1. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

Interesting!!!