Creating unique indexes with no case sensitive?

Hello, I'm looking for the best way to implement a unique constraint or index which doesn't accept a string two times, that is, If I wrote in a table the string 'Rafael' and later if I intented to write the string 'RAFAEL' It doesn't permit this... Because UNIQUE only validate against exactly strings I can't use only this word... I hope someone can help me! Thank's in advance! Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8tqae1$s6h$1@nnrp1.deja.com>, rafaelpadilla@my-deja.com wrote: > Hello, > I'm looking for the best way to implement a unique constraint or index > which doesn't accept a string two times, that is, > If I wrote in a table the string 'Rafael' and later if I intented to > write the string 'RAFAEL' It doesn't permit this... > > Because UNIQUE only validate against exactly strings I can't use only > this word... > > I hope someone can help me! > > Thank's in advance! > > Sent via Deja.com http://www.deja.com/ > Before you buy. > You do not state your version, so we do not know what exactly is available to you. I believe in 9.2 you can create a unique index on UPPER(name). If not, you can always create a trigger to populate a field with the upper case version of the name, and put a Unique constraint on that. Hope this helps, Will Sent via Deja.com http://www.deja.com/ Before you buy.

If your engine can't handle case insensitive searches, then my guess is that you'll have to write this as straight code. Any trigger capability would follow the case insensitive searches! Is nice() available, and does it give you the options you want? From memory - initial letters upper case, all other letters lower case? The old way was to force all uppercase or lowercase. If you can afford to store the string twice, then use construct like word_field_searchCase CHAR( x ), # Upper, or lower case only word_field_displayCase CHAR( x ), # Case to be used in displays From your note, I think you want the string recorded once, and once only - try validating input and update by checking the word using mixed case before storage ... "WHERE word_field MATCHES '[Rr][Aa][Ff][Aa][Ee][Ll]' " ... or "WHERE (word_field[1] = 'R' OR word_field[1] = 'r' )", "AND (word_field[2] = 'A' OR word_field[2] = 'a' )", "AND (word_field[3] = 'F' OR word_field[3] = 'f' )", "AND (word_field[4] = 'A' OR word_field[4] = 'a' )", "AND (word_field[5] = 'E' OR word_field[5] = 'e' )", "AND (word_field[6] = 'L' OR word_field[6] = 'L' )" Both of those constructs can be created using a for loop over the length of the string. When writing data, you would need an exclusive write lock to the whole table before validation, releasing the lock after writing the data. <rafaelpadilla@my-deja.com> wrote in message news:8tqae1$s6h$1@nnrp1.deja.com... > Hello, > I'm looking for the best way to implement a unique constraint or index > which doesn't accept a string two times, that is, > If I wrote in a table the string 'Rafael' and later if I intented to > write the string 'RAFAEL' It doesn't permit this... > > Because UNIQUE only validate against exactly strings I can't use only > this word... > > I hope someone can help me! > > Thank's in advance! > > > Sent via Deja.com http://www.deja.com/ > Before you buy.