At 10:24 AM 9/08/2007, you wrote:

>Hello,
>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));

>Then I tried unique expression index instead of unique constraint:
>CREATE UNIQUE INDEX TEST_IDX1_COL1 ON TEST COMPUTED BY (UPPER(COL1));
>To my surprise this index acts like a constraint watching for
>uppercase duplicity of incomming values. I used FB 2.0.1
>
>Is unique expression index a valid way to enforce uniqueness and case
>insensitivity for a column ?

An expression index merely forms an index entry based on the output
of the expression. It's not a validation check, per se. Still,
under some conditions, it might be useful for your users to get a
uniqueness violation on the expression rather than a validation error
on the data itself.

>Are there better ways how to do it ?

The CHECK constraint is the usual way to perform validation.

./heLen