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

[Dustin Sallings]

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