On 2019-10-30 3:12 a.m., Richard Hipp wrote:
On 10/30/19, Darren Duncan <dar...@darrenduncan.net> wrote:
Ideally a PRIMARY KEY would have no restrictions that a UNIQUE constraint
lacks;
they are conceptually the same thing, a subset of the columns of the row
that
uniquely identifies the row in the table, and designating one as PRIMARY is
completely arbitrary in that sense.
For rowid-tables in SQLite, the PRIMARY KEY is really just a UNIQUE
constraint. But that is kind of a bug that is retained for historical
compatibility - not something to be proud of. Let's ignore that case
for the moment.
In a WITHOUT ROWID table, the PRIMARY KEY really is the key used by
the B-Tree algorithm to locate records on disk. Hence, the PRIMARY
KEY really does need to be stored on disk. Theoretically, one could
have the B-Tree algorithm itself compute the values of keys
on-the-fly. But that is not possible in SQLite without significantly
rearchitecting the system. Expressions are evaluated using multiple
opcodes in the bytecode engine, but B-Tree search is a single opcode.
It is unclear how the B-Tree engine could be convinced to run multiple
byte-code operations in response to each key comparison.
All that said, it wouldn't really be a problem use a STORED generated
column as a PRIMARY KEY, since the value of the STORED column is
sitting there on disk and is hence readily available to b-tree. In
fact, that might just work if I merely remove the restriction. But
using a VIRTUAL generated column as a PRIMARY KEY would be an issue.
Thank you.
If I wasn't clear, it was specifically the STORED variant I was advocating
supporting with PRIMARY KEY, that provides all the benefits I looked for, which
I felt in theory be as easy to implement as a normal column PRIMARY KEY because
the value would be calculated before insertion.
So I request that the draft document be amended like as follows.
Take this line:
"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."
... with optional comment about possibly being relaxed in the future.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
