Re: unique in two not so unique columns

Hi Thomas,

Ic. I'd think a clean solution would be two user tables:
one with the unverified data and one with the verified.
If a user gets verified, just do a

insert into realuser
select ... from candidate;

How is this?

Regards
Tino

--On Samstag, 2. November 2002 03:37 -0600 "Thomas T. Thai"
<tom@minnesota.com> wrote:

> On Sat, 2 Nov 2002, Tino Wildenhain wrote:
>
>> Hi Thomas,
>>
>> --On Samstag, 2. November 2002 00:58 -0600 "Thomas T. Thai"
>> <tom@minnesota.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.
>