Affects Version/s: 2.4
Fix Version/s: None
Security Level: All
it says that MySQL and SQLite use AUTO_INCREMENT by default. The generated SQL for creating an ID field with GENERATOR_TYPE_AUTO looks like this (abbreviated
If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. [...] Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table.
So in other words, if you remove an entity and then create a new one, the new one will have the same id as the previously deleted one. If you do both operations on the same entitymanager, id references (in proxies f.x.) will suddenly get confused and point to something else (at least that's my current theory..)
The point is: SQLite doesn't act like MySQL as the documentation implies, and IMHO SQLite's current behaviour makes it useless for more complex scenarios. I've found some reference to this problem here:
Unfortunately, it doesn't mention any solution. The problem is that you can't override the columnDefinition, because it would have to be "INTEGER PRIMARY KEY AUTOINCREMENT", but then, you get an exception, because the Platform appends ", PRIMARY KEY(id)", so it's defined twice