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

[Johannes Erdfelt]

On Thu, Oct 10, 2002, Keating, Tim <TKeating@origin.ea.com> wrote:
> A better way to deal with this might be to break up your tables along
> functional lines. Right now it sounds like you are trying to store login
> credentials and user information in the same table. A safer way to do this
> might be (in pseudo-DDL):
> 
> Table LoginCredentials (
> 	int login_id PRIMARY KEY,
> 	char[] username UNIQUE,
> 	char[] password_hash
> )
> 
> Table UserInfo (
> 	int user_id PRIMARY KEY,
> 	char[] name,
> 	char[] emailaddress
> 	.
> 	.
> 	.
> )
> 
> Table LoginToUser (
> 	int login_id,
> 	int user_id
> )
> 
> Now, you can add rows for both username=username AND username=email address
> to the LoginCredentials table, use a much simpler query to log them in, and
> then retrieve their personal info through the LoginCredentials ->
> LoginToUser -> UserInfo join.
> 
> This isn't perfectly normalized, since email address is stored twice, but
> since you are overloading the purpose of email address anyway, it may be a
> better model for you. It also solves the original problem, since there is
> only one unique column.

And then I can assume any error that violates a unique constraint must
be the one unique column.

This is an interesting idea and looks like it would functionally work,
assuming transactions are used. I do use transactions and it would be a
reasonable requirement for the software I'm writing.

It's a little bit more complicated than I would have liked, but it's not
ugly and I think should be portable.

Thanks for the idea.

JE