I am just starting to explore SQLite in the context of personal projects and, more recently, Publii. So this is interesting. Didn't know about triggers.
I found one statement confusing in this post: "I can't add a primary key after the fact to my inputs table; it's not supported".
I have added PKs to existing tables before, what does the author mean by that?
Following up again, today I tried adding a primary key to a table in sqlite and didn't figure it out. I revisited the sqlite docs and found https://sqlite.org/faq.html#q11, showing that the alter statement is restricted to add, rename and drop.
It _does_ seem like you can add a primary key later in mysql and postgres, but I don't see any way to accomplish that in sqlite.
Oh that's very interesting. I am actually using a UI called "DB Browser for SQLite" and checking its logs shows that when I add a PK, it actually creates a new temporary table, copies over all data from the old table, deletes the old table, and then renames the new table.
So you were correct originally, it can't just add a PK.
I would have swore I read in the docs that you cannot add one after creating a table, but I cannot find a reference for that. Sounds like I made a mistake! Thanks for catching it!
I found one statement confusing in this post: "I can't add a primary key after the fact to my inputs table; it's not supported".
I have added PKs to existing tables before, what does the author mean by that?