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 -----Original Message----- From: Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
