You are missing the point. The rowid is assigned automatically if it is not specified (that is, if it is null on insert). This is the behaviour of the rowid. In all databases and filesystems ever invented anywhere in the multiverse by any carbon (even non-carbon) based lifeform, whether an ugly bag of mostly water or not.
The AUTOINCREMENT keyword is an entirely different beast that I have never seen actually required for any useful purpose (except that there appears to be a number of folks who seem to think that the rowid is not automatically generated without that keyword because they never RTFM or have some form of cognitive disability). It has nothing to do with choosing a primary key, a surrogate key, or an alternate key. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Richard Damon >Sent: Friday, 24 November, 2017 10:33 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table >creation [Bug] > >On 11/24/17 3:51 AM, R Smith wrote: >> >> On 2017/11/24 5:23 AM, Peter Halasz wrote: >>> As for whether I need to use AUTOINCREMENT, it seemed like a good >>> idea to >>> avoid rowid reuse, but I can avoid using it for the sake of >>> optimization, >>> so probably will. >> >> I agree with Keith and has many times mentioned this before >(apologies >> to others for the déjà vu). You as the programmer / designer should >> always be in control of how and why a new ID is assigned. >> >I would disagree here, in many cases the ID (rowid) is a purely >internal >attribute with the purpose of accessing the data. It may have no >problem >domain significance. If the primary key's purpose is purely data >access, >then letting the data access layer handle it makes sense. The one >case >where it makes sense for the programmer / designer to take control of >the PK is if the domain naturally has an identifier that would be >suitable for the key (an reasonable sized integer that is naturally >unique), The designer should also be fairly certain that it will >remain so. >> It feels like a saving to let the DB engine do it for you, but it >> isn't really. What you save in a bit of code that decides the new >ID >> before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you >> typically have to catch up afterward in code to do the usual >> get-LastInsertedID and then pop that in wherever stuff wants to >link >> to the new item. It's a zero-sum gain really. >The big issues with this method is if two processes both try to >create a >new item at the same time, one of them is going to get an error and >have >to redo its work. If you start by creating the record with an >autoincrement id, and then getting the ID used, then you remove the >need >to handle the error on the simultaneous creation. >> >> I'm not even a big fan of Integer IDs, I think codes / UUIDs are >best, >> but obviously the speed/size gain with an integer key (especially >> INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored. >> >Making a UUID or other 'big' key the primary access key will increase >the cost of looking up a record for ALL purposes. For small tables, >it >might not be measurable, but small tables are less apt to need that >sort >of PK either. It can make sense to use a key like that as an External >Key to describe the record to the outside world. Perhaps if the ONLY >accesses to a table are via this 'big' key, and very rarely by some >other key/field, making the big key the primary key would make sense. >> Disclaimer: This is only my opinion, well, I'm not completely alone >in >> it, but it is still an opinion and not a general SQL prescription. > >-- >Richard Damon > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
