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. -- 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