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

[Dustin Sallings]

Around 16:17 on Oct 10, 2002, Johannes Erdfelt said:

# 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.

	Oh, sorry, missed the point.  :)  At the point where you get an
exception on insert, you can do something like this:

	select username, email from users
		where username = ?
		or email = ?

	And do the comparison in the exception handler.  This assumes, of
course that it *is* an exceptional state (i.e. people *usually* don't try
to get usernames that already exist, or share the same email address with
lots of other users).

# 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. ____________
# >
#
# _______________________________________________
# 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. ____________