How do you impose a case insensitive unique constraint on a Firebird field value?

Question

I want to add a field called email to table AGENT. I want the values of AGENT.email to be unique across the table and I want this uniqueness to be case insensitive.

For example adding "[email protected]" would violate the constraint if "[email protected]" already exists in the table. How do I do this?

Answer 1

You can add a unique index on lower of email column on the table like so:

create unique index email_unq_idx on agent computed by (lower(email));

Answer 2

To impose a case insensitive constraint, the best way is to create the column with a case insensitive collation like unicode_ci_ai - this collation is also accent insensitive, which you may or may not want -, and add a unique constraint (or unique index) on that column:

create table addressbook
(
    id integer not null,
    emailaddress varchar(150) character set utf8 collate unicode_ci_ai,
    constraint pk_addressbook primary key (id),
    constraint uc_emailaddress unique (emailaddress)
);

or to add the field to an existing table:

alter table addressbook
    add emailaddress2 varchar(150) character set utf8 
            constraint uc_emailaddress2 unique collate unicode_ci_ai

Alternative collations would be unicode_ci (only case insensitive) or other built-in case insensitive collations depending on the character set used, or to create your own specific collation.

The advantage of this solution over the one presented by Gurwinder is that it also allows you to select case (and accent) insensitively without having to use lower in your where clause.