On Wednesday, 1 March, 2017 14:12, Deon Brewis <de...@outlook.com> wrote:
> "But look how much space and processing time it would take up" > Can you clarify what you mean by "space" ? > The processing time argument I understand. I doubt there is any "space" requirement at all. In fact, since you are not carrying an extra index, the space requirement will be significantly reduced. Your only real concern would be that on INSERT and UPDATE operations the _prepare would take a little longer since it would have to retrieve and generate the trigger code for the statement. You pay the price during statement prepare, but I doubt that the actual statement execution time will be much affected (the execution would have to do a "lookup" in the unique index anyway to determine whether the operation meets the unique requirement) as you are merely substitution one operation for another like operation. Of course, the internal unique check is done entirely inline, whereas the trigger method is done by running additional VBDE code -- though of course the overall time to run the trigger may approximate the time taken to maintain an the extra index -- in which case you would save the space used by the extra index and have no (or a minor) execution time penalty on insert/update operations. > I think this is one of those things that if the database engine doesn't > internally support it, it can't really be emulated. > > Sure would be nice to have INCLUDE columns support (here and in other > places). > > - Deon > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Wednesday, March 1, 2017 12:57 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Non-unique columns in unique index > > > On 1 Mar 2017, at 8:21pm, David Raymond <david.raym...@tomtom.com> wrote: > > > The trigger version you asked about would look something like the below > I believe. More risky than having the two indexes, but should work. > (Famous last words) > > I have no doubt it would work. But look how much space and processing > time it would take up. Far simpler and clearer just to create the two > indexes. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
