[andai]

In the first example, there's a a second thing that surprised me: you delete an entity and it's unique ID gets reused? Is that a good idea?

I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.

[deepsun]

I think that's a security vulnerability.

If a parent table ID gets reused, then it's a potential to expose data to a wrong user -- security broked.

[zarzavat]

That's correct but SQLite was never designed to be a production database in the first place. It can be used as a production database but only if you know what you're doing, and presumably anyone who knows what they're doing knows about the AUTOINCREMENT keyword because it's one of the first things you learn about SQLite.

[nick__m]

I disagree, SQLite is a production embedded database, the extensive test¹ suite is a testament to that. It's just has different default and behavior than a database designed for being served to multiples simultaneous writers.

1- https://sqlite.org/testing.html

[bruce511]

>> you delete an entity and it's unique ID gets reused? Is that a good idea?

That's default behavior, but it can be altered when creating a table. See;

https://sqlite.org/autoinc.html

[LtWorf]

Where do you see that they get reused from that link?

[echoangle]

> The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

[LtWorf]

What should it do once you hit MAXINT? Honest question…

[echoangle]

I don’t think people are criticizing the MAXINT thing. The problem is that IDs are already reused when you create 10 rows, delete 5 and then make a new one. Normal DB engines just keep counting afaik so the new ID will still be one that has never been used before.

[bruce511]

In SQLite the max int is 64 bit. So a rather large number (about 9 billion billion, aka 19 digits). That's a lot of rows to add.

I would suggest if you are storing that much data, SQLite may not be the correct engine. (And you probably shouldn't be using an Int primary key.)

It's a good question to ask, but probably not a concern for most of us.