On Wednesday, 30 October, 2019 06:41, Dominique Devienne <ddevie...@gmail.com> wrote:
>On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp <d...@sqlite.org> wrote: >> On 10/29/19, Keith Medcalf <kmedc...@dessus.com> wrote: >> > Before you change anything, I think that is incorrect for the various >> > datetime functions. I think they SHOULD be permitted in CHECK >> > constraints and in generated always columns, whether those are >> > stored or virtual, whether or not parameters are given since they >> > are constant. They should not be permitted in indexes however unless >> > they are pure (deterministic). >> i believe the purpose of a CHECK constraint is declare an eternal >> truth about the database content, not merely something that was true >> at the point in time when the content was first inserted. Am I wrong? > The kind of CHECK constraint Keith mentioned would run afoul of > https://www.sqlite.org/pragma.html#pragma_integrity_check so you're > probably right that allowing these non-deterministic function is not > a good idea (on 2nd thought...) --DD Hmmm. Yes, I believe that is correct that check constraint on a table is a truth about the table and should always evaluate to True (or NULL), and should not become False due to the passage of time (or anything else). A tuple that passes a CHECK constraint on INSERT (or UPDATE) should eternally meet that constraint, so allowing a non-deterministic reference in a CHECK constraint could violate this. I do not think that such a restriction would apply to the value of a generated column though UNLESS that column is used in a CHECK constraint or an index, and even then only for a virtual generated column (a stored generated column does not have this problem, however, and does not need to be restricted, since the value can only change when the tuple is inserted or updated). I can see this getting very complicated very quickly. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
