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

[Johannes Erdfelt]

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