> 1. If you define a column with NOCASE and later an index without, it > won't be possible to insert two values differing only by case, > because the column will reject it.
Of course it will accept the value, unless you declared the column unique so that a unique index is created using the nocase collation. Whether or not the index with the binary collation is unique is irrelevant since the two values are different, as far as that index is concerned. > 2. If you define a column with default collation and later an index > with NOCASE (successfully, because at the time of creation no two > values differed only by case) then henceforward it *also* won't be > possible to insert two values differing only by case, because the > index will reject it. This would be true only if the index created with collation nocase were a unique index. Otherwise, it would not care that you inserted rows with duplicate case-folded values. > I suppose the current state of affairs -- where indexes have collation > properties independent of the columns they index -- is more > historical anomaly than design. It's hard to see a reason to carry it > forward in SQLite4. With this I agree but generally because I do not see any purpose in having multiple collations apply to the same attribute under different circumstances. Perhaps there may be circumstances where it is useful, but I have never ever seen one. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users