> Alright, so I made the modifications as so to the Categories table: > > CREATE TABLE [Categories] ( > [CatID] INTEGER PRIMARY KEY AUTOINCREMENT, > [Name] CHAR COLLATE NOCASE); > > CREATE UNIQUE INDEX [idxCatName] ON [Categories] ([Name] COLLATE > NOCASE); > > Does this make more sense? > > I have a row with Name field stored as CPU. Pre-modification to the > table, > I ran the SQL command [ select * from Categories where Name='cpu' ] and > received no results. Modified the table and ran the same command, and I > got a result. So both index and table is created with NOCASE.
You don't really need the collate nocase in the index declaration, since the column Categories.Name already has "collate nocase" attached, and that affinity (declaration) will follow around every use of the column in every operation unless you override it. You could declare the column unique right in the table definition instead of declaring the index separately. CREATE TABLE [Categories] ( [CatID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] CHAR COLLATE NOCASE UNIQUE); The only difference between doing this and manually creating the unique index is that you cannot specify an index name (it gets an auto-denerated name) and you cannot override the collating sequence (collate nocase) attached to the column declaration, nor can you override the index order (it is always ascending and you cannot specify descending). It just makes your declarations somewhat shorter and makes it easier for humans to see that [Name] is unique without having to look for the index declaration. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
