At 02:50 PM 23/11/2003 +0700, you wrote:

>I am trying to create a table to store unique email addresses. I want to
>separate the local and the host part ("local@host") since they
>(especially host) appear often in WHERE clauses and thus indexing both
>parts would be a benefit.
>
> create table emails (
> local varchar(126) not null,
> host varchar(72) not null,
> email computed by (local||'@'||host)
> );
> create index emails_local on emails(local);
> create index emails_host on emails(host);
>
>Now, for this table, the problem is expressing unique constraint for
>'email'. This won't work of course since computed fields currently
>cannot be indexed:
>
> unique(email)
>
>This also won't work because '@' is not the name of a field:
>
> unique(local, '@', host)
>
>This works but is incorrect since it can mistakenly say that
>'sean@...' and 'seanwilliam@...' are duplicates:
>
> unique(local, host)
>
>There are several alternatives, none is perfect, but so I think far the
>best compromise one is:
>
> create table emails (
> local varchar(124) not null,
> ampersand char(1) default '@' not null check (ampersand='@'),
> host varchar(72) not null,
> email computed by (local||ampersand||host)
> );
>
>Any other better alternatives?

Thre things:
1) the "@" symbol makes absolutely no difference to the uniqueness of your
local + host combination. You don't need it anywhere. The fact that the
local and the host pieces are in separate columns guarantees uniqueness.
2) because of the length of the two fields, a surrogate primary key is
wanted here.
3) you don't need the computed column in storage (see example below)

Here's the DDL:
create table emails (
email_id BigInt not null primary key,
local varchar(124) not null,
host varchar(72) not null,
constraint email_uq unique(local, host));

Example:
select email_id,
local ||'@'||host as email
from emails;

For searching, in your app write a breakapart routine for email addresses,
so you can supply the individual column values (in the right order) and use
the index that was created for the constraint.

Depending on how well you parse input, you might need to maintain an
indexed uppercase proxy column for the two columns and make search
conditions case-insensitive. The practice some people have of using mixed
case in their email addresses is another major reason not to use them as
keys. They are *highly* non-atomic.

Helen