Re: unique in two not so unique columns

On Sat, 2 Nov 2002, Tino Wildenhain wrote:

> Hi Thomas,
>
> --On Samstag, 2. November 2002 00:58 -0600 "Thomas T. Thai"
> <tom(at)minnesota(dot)com> wrote:
>
> > I have two columns in a table:
> >
> > email varchar(64)
> > verified boolean
> >
> > How do I make a check for unique email that is verified while allowing for
> > non-verified emails to be not unique?
>
> Before thinking of a solution for this in PG, I dont
> see why you need this requirement in the first place:
> whay should the very same e-mail be both verified and
> unveryfied? And even more - why should be more then one
> row telling you this very same e-mail is unverified?
>
> Is this only an example which does not serve very well
> or is there a bigger picture?

It's for a user authentication system. User registers, but I want to
verify their email address before allowing them access. There are more
fields in that table than what I showed (like userid, etc.).

If I don't verify their email address, then anyone can sign up and use
someone else's email address, there by preventing the ligitimate owner of
that email address to register in the system. Once the email address is
verified, I don't want other users trying to use that email address again.
I'm currently doing a SELECT to check the conditions, but I wanted a
backup solutions so it's more transaction safe.