I do need to maintain the uniqueness of those two columns. I suppose I could drop the UNIQUE constraint an check for uniqueness in my code but I'm a great believer in having sqlite do as much as possible for me.
The "Blue" vs "blue" issue is addressed by COLLATE NOCASE (at least I think it is - I should test it!). The continuity of the sequence column is handled in my application code since I couldn't think of a way to have sqlite do it for me. I'm still somewhat surprised that creating a unique index with columns sorted descending, coupled with an UPDATE naming that index still resulted in a unique constraint violation. I guess sorting the columns in an index serves a purpose other than affecting the order in which rows are processed. Thanks to everyone for the clever suggestions on how to do this. I guess I will have to decide if any of them are any "better" than what I'm doing now which is selecting the rows whose sequence to be changed in desc order of sequence than updating each one in a loop in my application code. On Sat, Sep 7, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote: > Message: 23 > Date: Fri, 6 Sep 2013 17:45:59 -0400 (EDT) > From: j.merr...@enlyton.com > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UPDATE question > Message-ID: <1378503959.951430...@apps.rackspace.com> > Content-Type: text/plain;charset=UTF-8 > > I propose that you remove the unique index because SQLite does not handle > the update case the way you want. (I'd call that a bug, but sometimes > "liteness" means that Dr Hipp can say "I'm not going to fix it" and we have > to respect his decision.) > > Is there a reason other than "if you have a particular kind of bug in your > code, you could end up violating the [Name/Sequence is unique] rule" to > keep the index, when its presence causes you trouble? (Perhaps the answer > is "users edit this table manually using other software" so you need the > index to keep them from screwing up. But I doubt it, or they'd have > complained what a pain it is to add a new row in the middle!) > > There is little to prevent you from having other bugs that might be > equally bad -- > > - putting both "Blue" and "blue" in the Name column (with separate sets of > Sequence values) when both values shouldn't be there because the business > context says they're the same > - have Sequence values not starting at 1 (e.g. 2 3 4) for a particular > Name -- perhaps that wouldn't cause any trouble in other logic, but it > probably would > > If you wanted to, you could have your initial "open the database" code > check for duplicates across those columns (and that 1 is the lowest > Sequence for each Name) -- then at least you'd know that you'd had one of > those bugs. > > J. Merrill > Pete lcSQL Software <http://www.lcsql.com> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
