I'm ALWAYS looking for a faster query (Who isn't? -- Except those edge
cases where management thinks the software is broken because the query is
TOO fast and doesn't trust the results) but the loss of some common use
functionality kind of has me wondering "Why?"
Well yes but...
Firstly, using the last_insert or equivalent function is not the SQL way to do things, but a rather clever optimization provided by
most (if not all) SQL implementations that supports a very specific method of adding things to tables. You could for instance
rather remember (or find) the last added primary key through SQL, and then simply add data where YOU assign the primary key values
(in stead of letting the autoinc do its thing) and then you have the key to insert in as many tables as needed - as it should work.
The autoinc is just an added help for humans to make it easier since "everybody uses integer primary keys anyway", and it ensures
unique values from a database-engin side removing that responsibility from your code. As far as SQL is concerned though, if you use
a lot of matching keys to identify data in multiple tables, you should really be specifying those keys yourself and not rely on the
various shortcuts.
It's probably faster even to specify Key values than wait for the DB engine to run its own Autoinc code for every insert (though
this is very fast too).
Now disregarding all the above - The very only reason you would use the WITHOUT ROWID optimization on any table is precisely because
you are NOT using an integer primary key but because you are adding proper text values as the Primary key, so even if you could use
the last_insert function to get a valid rowid then it won't help you because the table is referenced via text primary key and there
is nothing useful the linked table can do woth the last_insert value... unless you are linking tables to each other using the actual
rowid, which would be the very worst DB decision ever, so I'm sure that is not the case.
Even in an after-insert Trigger (as another poster remarked) you would really need to know the Primary Key by direct reference
already since the DB isnt making up it's own on an insert, I'm not sure how it would be of any value - but I might be
misunderstanding the suggestion.
As to your question of: WHY? I'm sure other posts describe the benefits well so I won't repeat it except to say that it is a very
case-specific benefit and there is no need to use it for the normal tables you described.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
