Typically none, though in very rare cases there may be one or two out of a dozen or so tables.
Generally speaking, I have found no particular advantage in most circumstances to having "integer primary key" with the "AUTOINCREMENT" property (that is, guaranteed ascending unique assignment larger than any "integer primary key" ever previously inserted in that table). The simpler "integer primary key" without AUTOINCREMENT still guarantees that the "integer primary key" is unique and is sufficient for the common use case of assigning a unique alternate key to the tuple in the table (which will become the primary key) to be used to enforce referential integrity. That is, the proper key is contained in the row data and is an alternate key to the row and the "integer primary key" is simply used as a simple shorter "alias" candidate key for the tuple. This also applies there the database is designed using a "mastertable" (a table that contains nothing except references to its multiple child tables which contain the data, but no actual data assigned any column of the "mastertable" itself), a design common if implementing a "network extended" type hierarchical model on top of a relational database, for example. The exception where AUTOINCREMENT is useful is where the rowid alias can itself be "overloaded" with meaning rather than strictly an "internal use" only candidate key and perpetual "uniqueness" is a contraint of the assigned overloaded meaning. That said, however, such tables often end up being parents in a relationship (not children) and therefore generally the persistence requirement is met by simply not deleting the parent -- which often ends up being part of the overloading requirement and therefore rendering AUTOINCREMENT moot -- that is, if the "integer primary key" is overloaded with meaning -- as in JobNumber or DocumentNumber or somesuch -- then once assigned, removing the tuple is prohibited by external (ie, Business) requirements. --- 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 Hipp >Sent: Friday, 16 March, 2018 09:37 >To: General Discussion of SQLite Database >Subject: [sqlite] How many AUTOINCREMENT tables are in your schema? > >This is a survey, the results of which will help us to make SQLite >faster. > >How many tables in your schema(s) use AUTOINCREMENT? > >I just need a single integer, the count of uses of the AUTOINCREMENT >in your overall schema. You might compute this using: > > sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | >wc -l > >Private email to me is fine. Thanks for participating in this >survey! >-- >D. Richard Hipp >d...@sqlite.org >_______________________________________________ >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
