--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 10:24 AM 9/08/2007, you wrote:
> >please, how to make a varchar type column unique and case insensitive ?
> >I tried to use unique constraint:
> >CREATE TABLE TEST (COL1 VARCHAR(4) NOT NULL);
> >ALTER TABLE TEST ADD CONSTRAINT UNQ1_TEST UNIQUE (COL1)
> >USING INDEX UNQ1_IDX_COL1;
> >But such constraint is case sensitive and allows both 'abcd' and
> >'ABCD' to be stored.
>
> It needs a CHECK constraint as well:
>
> ALTER TABLE TEST
> ADD CONSTRAINT FORCE_UPPER_TEST
> CHECK (COL1 = UPPER(COL1));
>
>
> >Are there better ways how to do it ?
>
> The CHECK constraint is the usual way to perform validation.
>
> ./heLen
>

CHECK as you suggested actually forbids the lowercase values to be
stored at all. But I want to store values as they come from the user
and treat them as case insensitive values.
Thus values should remain as they are comming and only validation,
check, constraint, index or whatever should protect against duplicates
in uppercase form of values.

What Alexandre wrote about putting constraint UNIQUE into action by
the use of a case insensitive collation seems a nice idea.
Unfortunately, there is no case insensitive collation for character
sets for slovak language. I don't feel myself able to create such
collation now. Therefor, it seems that I have to go with the unique
expression index.

Thank you all for your opinions, valueable explanations and knowledge
share about possible solutions.

Boro