New topic: SQLite Case insensitive Unique Column?
<http://forums.realsoftware.com/viewtopic.php?t=47236> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message Karen Post subject: SQLite Case insensitive Unique Column?Posted: Sat Mar 09, 2013 8:21 pm Joined: Fri Sep 30, 2005 8:53 am Posts: 980 I am trying to create a case insensitive Unique EMail column and am failing miserably... (yes I know the spec says the first part should be case sensitive - but that is rarely if ever enforced by Email servers these days and effective duplicate EMail addresses would be a problem for what I am doing) I've tried defining the column as : Email TEXT NOT NULL COLLATE NOCASE UNIQUE But I can still add records with Email records that are the same except for case Then I added: CREATE UNIQUE INDEX UniqueEmail ON Users (Email COLLATE NOCASE); but that still did not help... Is it because we are using UTF8? Is there a way to do this? Thanks, - karen Top Sky Stream Post subject: Re: SQLite Case insensitive Unique Column?Posted: Sat Mar 09, 2013 9:36 pm Joined: Sun Nov 23, 2008 12:27 am Posts: 398 Location: New Jersey Just curious.. what if you insert the data into the column as all lower or upper? This will ensure all case is the same. _________________ - Rich Top Karen Post subject: Re: SQLite Case insensitive Unique Column?Posted: Sat Mar 09, 2013 9:49 pm Joined: Fri Sep 30, 2005 8:53 am Posts: 980 Sky Stream wrote:Just curious.. what if you insert the data into the column as all lower or upper? This will ensure all case is the same. I thought of that... But mixed case is easier for users to read so I wanted to avoid that... And that is more important for another field that is not Email addresses and needs to be more human readable... I could create a second field that is all one case and store both... but I think that is error prone. Top timhare Post subject: Re: SQLite Case insensitive Unique Column?Posted: Sun Mar 10, 2013 1:59 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12130 Location: Portland, OR USA I believe that NOCASE requires an extension that RB doesn't link in. Top Dale Post subject: Re: SQLite Case insensitive Unique Column?Posted: Sun Mar 10, 2013 2:09 pm Joined: Thu Mar 01, 2007 2:02 pm Posts: 227 Location: Sunny (generally!) Southern California Why are you concerned with the case when the data is being stored in the database? I would think that any conversion to lower case (or upper if that is needed) would best be done in the select or data manipulation statements, as in: Quote:SELECT ... WHERE lower(itemindatabase) = lower(thingyouarelookingfor) That way you can save the readability and still match if the cases aren't the same. And you still have the mixed case for when you need to match case. - Dale _________________ ----- Real Studio 2012r1 on Windows 7 (64 bit) ----- It has been said that politics is the second oldest profession. I have learned that it bears a striking resemblance to the first. - Ronald Reagan Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
