On 2019-10-30 12:52 p.m., Keith Medcalf wrote:
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.
Okay, I think we're on the same page, so to be clear the scenario I want to have
supported is that a STORED generated column can be used in the PRIMARY KEY of a
WITHOUT ROWID table.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
