[DB-SIG] Portable method of determining which column wasn't unique?

[Johannes Erdfelt]

It still doesn't tell me which one was taken. Just that one of them was.

In this case it's the difference between just informing them that they
need to pick a different username, or helping them find the other
account they created.

JE

On Thu, Oct 10, 2002, Dustin Sallings <dustin+pydbsig@spy.net> wrote:
> Around 15:57 on Oct 10, 2002, Johannes Erdfelt said:
> 
> 	The correct way to do it is to have a unique index that spans
> those two columns.  If your insert fails, the name's taken.
> 
> # On Thu, Oct 10, 2002, Bob Gailer <ramrom@earthling.net> wrote:
> # > At 02:52 PM 10/10/2002 -0400, you wrote:
> # >
> # > > I have a table which has multiple unique columns on it. My problem is that
> # > > when I insert a row which violates the unique restraint, I can't figure out
> # > > which column caused the problem.
> # >
> # > Just a stab in the dark - but I am suspicious of a data model that requires
> # > unique values in two (or more) columns. Can you verify that this is really
> # > needed?.
> #
> # Yes. The two values are a username and an email address.
> #
> # username is unique for obvious reasons and the email address is unique
> # because it provides an alternate means of logging in.
> #
> # > Also are the users of the system providing these values directly?
> #
> # Yes. The user chooses their own username and their email addres is their
> # own.
> #
> # > You could :
> # >    select * from table where unique-column1 = proposed-new-value1
> # >    select * from table where unique-column2 = proposed-new-value2
> # > If you get a row in either case then you know the corresponding new value's
> # > already in use,
> #
> # That has the same races as the possible solution I came up with that I
> # described at the end of my original email.
> #
> # Another insert came come in between the select and the insert.
> #
> # I was hoping to make this efficient and not require explicitly locking
> # the table since the table receives queries often.
> #
> # I was also hoping to keep it portable across multiple databases (hence
> # the relevance to db-sig)
> #
> # JE
> #
> #
> # _______________________________________________
> # DB-SIG maillist  -  DB-SIG@python.org
> # http://mail.python.org/mailman/listinfo/db-sig
> #
> #
> 
> --
> SPY                      My girlfriend asked me which one I like better.
> pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
> |    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
> L_______________________ I hope the answer won't upset her. ____________
>