[DB-SIG] Portable method of determining which column wasn't u nique?
Thu, 10 Oct 2002 16:41:49 -0400
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.