PySQLite and Autoincrement Keys

[achrist at easystreet.com]

Gerhard Häring wrote:
> 
> achrist at easystreet.com wrote:
> > If I have a table with an autoincrement key in SQLite, and if I
> > delete the row with the largets key, then add a new row, will or
> > might the key value be re-used?
> 
> You could have easily checked that for yourself, haven't you?
> 

Yes, but I never believe anything one just one person's account.
When I encountered the problem, I quickly rewrote the data access 
part of the big app I'm in a hurry on to just flag deleted records, 
kind of like dBase used to.  By the time I got it re-written, I 
wasn't sure if I really needed to do that, so I posted an inquiry. 
I was also inquiring to get reaction of others to see if this is
something that would be the basis for a reasonable request for a
fix to the author of SQLite.  If his package isn't doing what the
others all do,  maybe it would be a good idea. 

> I just tested it and it seems ids are being reused.
> 
> > Is this good or bad?
> 
> This depends. Mostly bad IMO, because you don't get the behaviour of
> SEQUENCEs in other RDBMS that way.
> 
> Also SQLite doesn't support any foreign keys, so you'd have to work
> around this limitation by multiple column CHECK CONSTRAINTs. I'm not
> sure if that's really feasible, as I've never done it myself.
> 
> Oh well :-(
> 

I think it's bad, too.  Is there a way for cascading deletes? Even
if there is, it would not be good for my app, because it keeps lots
of data in memory while it runs, and I didn't think about cascading
deletes for all the dictionaries that modules of my program might
have tucked away.   The data is logically unreachable when a row 
is deleted.  Unfortuanately, it gets reachable again when a new 
row gets inserted with the same key.   


Al