On Wednesday, 30 October, 2019 13:23, Darren Duncan <dar...@darrenduncan.net> wrote:
>On 2019-10-30 12:02 p.m., Simon Slavin wrote: >> On 30 Oct 2019, at 6:56pm, Darren Duncan <dar...@darrenduncan.net> wrote: >>> "Generated columns may not be used as part of the PRIMARY KEY. (Future >>> versions of SQLite might relax this constraint for STORED columns.)" >>> >>> Replace with this: >>> >>> "VIRTUAL generated columns may not be used as part of the PRIMARY >>> KEY." >> What happens if the generated column is based on a calculation >> including rowid ? At the time the values are calculated, rowid might not >> yet be known. > I suggest one option is adding a restriction that rowid may not be used > directly or indirectly in a STORED generated column and only in a VIRTUAL one. The rowid value is always known so this is not an issue. > One way or another I believe this is surmountable and stored generated > columns in a primary key is worth it. In a rowid table, the primary key is the implicit or explicit rowid. "primary key(...)" in such tables is merely and alternate spelling of unique(...), and you can specify an index on a generated column computed from the (explicit) rowid. But since the rowid is already the primary key you cannot specify another one and if you do then your spelling "primary key" is automatically translated into the spelling "unique". However, you must actually spell "primary key" as "unique" to generate a unique index on a generated column in a rowid table even if you have not explicitly declared the rowid column. The restriction is that you cannot use a generated column as the primary key of a WITHOUT ROWID table, however, for those tables there is no rowid so something has to be the actual primary key since it cannot be the rowid. However, in order to use a generated column as a primary key in a WITHOUT ROWID table, the value would have to be stored. You can still create other indexes using the generated columns (whether virtual or stored) but you still need a stored primary key. Lifting the restriction on the use of a stored generated column as the primary key for a without rowid table is the only restriction that could be lifted. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
