Auto increment field in sqlite

[ctrochalakis]

When using sqlite3 sql backend the primary key doesn't get the AUTOINCREMENT keyword causing a mess in foreign keys when an object is deleted.

See also the last paragraph of ​http://www.sqlite.org/faq.html#q1

[Marc Fargas]

Can you elaborate a bit more on the "mess" part? sqlite does not understand AUTOINCREMENT as far as I know, you define a table as INT PRIMARY KEY and sqlite does the same as MS Access does (more or less).

[sebastian.hillig <sebastian.hillig@…>]

Deleting an object via the django-orm will result in deleting related objects. If you delete rows yourself (out of orm-scope) you have to take care of database consistency yourself in case of sqlite (and every other database that doesn't enforce fk consistency) and django would give you a nice error stating that there's something wrong, so please make sure you leave your database consistent. AUTOINCREMENT wouldn't be the solution to any problem since it only enforces one-time use of a primary key. Even if you would be inserting new rows from somewhere else, sqlite would add new rows with largest-pk + 1 as pk up to the point you reach pk == 9223372036854775807, then it would be starting to pick free pks from the beginning. Therefore marking this ticket invalid.

[django.bigfudge@…]

I'm not reopening this ticket myself, but I would like to petition for it to be reopened because of a specific problem with django_reversion, and likely other code intended to version django objects.

Quite reasonably, reversion versions are not deleted when an original object is deleted. However, versions do use the deleted objects' id to link back to that object. If using sqlite, when an object is deleted then it's pk is freed up for reuse. When this happens, reversion_versions link back to the wrong object, and in the admin it looks like there are more versions than there actually are ( you get the full history for any object which has had the pk in question).

Were Sebastian right that AUTOINCREMENT would only add new rows with largest pk+1 then this would still be a problem: the object with the largest PK could be deleted, and a new one would reuse this pk, and this would still confuse reversion. However, quoting from the SQLITE docs:

"The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database." ​http://www.sqlite.org/autoinc.html

So, if the AUTOINCREMENT keyword were added by django when creating tables then this would prevent reversion getting confused. I'll cross post this to the reversion people, but it seems to me that fixing django would be a lot easier than trying to make the reversion app compatible with sqlite.

[anonymous]

Duplicate of #10164

[k_bx]

Guys, I, as many others, use sqlite database to run local dev-server and for tests (production is running postgresql). I was writing test that needs to ensure that object is "recreated" (we decided to implement "edit" operation on complex object graph as first removing main object with it's dependencies and then re-creating main object with same pk, and then adding it's dependencies), so that it and it's foreign objects linking him are first removed, and then new one with same pk is created, and then new objects linking it are created (not setting their pk). As a first implementation (when I didn't know that pk can be reused in SQLite), what I did is to expect DoesNotExist error under old object's pk (which should be deleted). But now, since new one is created instead of that object, object under that pk is present in database.

Guarantee that pk is not reused under AUTOINCREMENT (well, at least in tests, since not many people have so many objects in tests) is a really strong feature here, without which I'm not sure what ways to go better (how do you ensure that two objects are not the same if you're able to reuse pk's now?).

So, I'd ask to take a pragmatic approach here and use AUTOINCREMENT in SQLite to provide guarantee at least for people not creating MAXINT-many objects (e.g. in tests and local development).

Thanks.