Обсуждение: unique in two not so unique columns
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?
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.
Вложения
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
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.
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. >
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