Per https://www.sqlite.org/draft/gencol.html thank you very much for adding this
generated columns feature!
I consider this to be a very powerful foundation for a variety of useful
features, in particular because it empowers generalized solutions to several
classes of problem.
In particular, this provides a generalized foundation for arbitrary unique
constraints and indexes.
Some SQL DBMSs have complicated syntax for declaring indexes or keys, say for
example case-insensitive uniqueness or indexing only a prefix of a character
string or various other things.
When generalized generated columns are supported as SQLite is going to do, the
syntax for key or constraint declaration can simply reference a column as a
whole and use the entire pristine column value; by default this means key or
index in a case-sensitive etc manner, but if there is a generated column that is
a lowercased version of a regular text field, one can put the unique constraint
on that column instead to get the case-insensitive uniqueness without
complicating the key/index declaration syntax to do so.
There is a lot of power here from such a basic foundation.
Related to this, I strongly encourage the SQLite developers to relax the
constraint on generated columns being used in a PRIMARY KEY sooner rather than
later.
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.
The benefits I ascribed to generated columns as a foundation would be greatly
weakened if a PRIMARY KEY can't use them.
Thank you.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
