[DB-SIG] Portable method of determining which column wasn't unique?
Johannes Erdfelt
johannes@erdfelt.com
Thu, 10 Oct 2002 15:57:32 -0400
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