On Wed, Oct 30, 2019 at 11:12 AM Richard Hipp <d...@sqlite.org> wrote:
> [...] But using a VIRTUAL generated column as a PRIMARY KEY would be an > issue. > FWIW, I depend on this feature in Oracle, and it works. Both my PRIMARY and FOREIGN keys are VIRTUAL columns, which combine two stored columns. Oracle even allows to ALTER TABLE to go from a mode where PKs and FKs are scalar and using stored columns, and another where those PKs and FKs are switched to (still scalar) constraints but using these VIRTUAL columns instead. The big advantage of this is that the upgrade (one way or another) "theoretically" writes nothing on disk since the stored columns do not change at all, only VIRTUALs and CONSTRAINTS are added/removed. An alternative to this design would be to use composite PKs and FKs, but our app depends heavily on efficient handling of SELECT ... WHERE pk_col in (:1), with :1 being bound to a collection/array of PK values, and I could not figure out a way to do the equivalent with a composite PK. (and that's also one reason why I've requested several times for a way to bind collections in SQLite, in a way that's not dependent on carray() which I'm not fond of since does not handle lifetime) So maybe it's not the best reason for VIRTUAL columns being allowed as PKs, but that's my $0.02. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
