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

[Keating, Tim]

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.

TK

-----Original Message-----
From: Johannes Erdfelt [mailto:johannes@erdfelt.com] 
Sent: Thursday, October 10, 2002 2:58 PM
To: db-sig@python.org
Subject: Re: [DB-SIG] Portable method of determining which column wasn't
unique?


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