Hi !


ygboro 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 dependends on the collation that you use.

If you use a case insensitive collation (like PT_BR) the unique
constraint will be case insensitive too.

> 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
>

Yes, it would work.

> Is unique expression index a valid way to enforce uniqueness and case
> insensitivity for a column ? Are there better ways how to do it ?
>
>

Yes and No :-)

Yes because it will give what you want.
No because conceptually a constraint is a "rule" that's different from
an index that is an artifact to speedy up searches.

But in fact FB creates an unique index for every unique constraint, but
this is just implementation detail, nothing would prevent that FB in the
future implements unique constraints with out indices.

But for your case the unique index on an expression will work ok. But
perhaps you could look for some case insensitive collation that will be
a more elegant solution IMHO.

Take a look if the character set/collate you use maps correctly the
accented/specials characters to upper case.

> Boro
>
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br