Thread: unique in two not so unique columns

unique in two not so unique columns

From
"Thomas T. Thai"
Date:
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?



Re: unique in two not so unique columns

From
Martijn van Oosterhout
Date:
On Sat, Nov 02, 2002 at 12:58:34AM -0600, Thomas T. Thai 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?

create unique index check_index on table(email) where verified = 't';

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Attachment

Re: unique in two not so unique columns

From
Tino Wildenhain
Date:
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?

Regards
Tino

Re: unique in two not so unique columns

From
"Thomas T. Thai"
Date:
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.


Re: unique in two not so unique columns

From
Tino Wildenhain
Date:
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.
>



Re: unique in two not so unique columns

From
Jean-Luc Lachance
Date:
Here is a simple solution:

Create two tables -- One with only validated email the other not
validated.



"Thomas T. Thai" 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly