[thelist] Free Tip about SQL

[Chris W. Parker]

rudy <mailto:rudy937 at rogers.com>
    on Friday, July 18, 2003 3:37 PM said:

Rudy,

It's always a pleasure to read your posts and actually learn something
from someone who's been around the block, per se.

I should also mention that after reading a post like this I tend to say
"HHHMMMMMMmmmmmmmm......." as in "Hhhmmm.... let me think about that for
a minute."

>>> SELECT id
>>> FROM users
>>> WHERE username = 'chris'
>>> AND coolfactor = 'infinite'
>>> AND babemagnet = 'yes';
> 
> this is exactly the method i was talking about, but a small
> refinement is necessary

[snip]

> you only have to do it on the "real" primary key, which in chris'
> example is probably just the username -- unless, chris, you were
> thinking of supporting the idea that two entries in the users table
> can have the same username with different coolfactors or babemagnet
> attributes...

No, two entries in the users table can not have the same users name. How
do I prevent this from happening? Well I don't know if it's the best way
to do it but when I receive the data I first query the database to see
if a record with the username given is already in there. Of course if
there is a record found the user is told so and told to 
do something about it.

So you're right that I could have (in the case where the table will have
only one username equal to 'chris') just used the username column to
qualify my query.

When I wrote that I was thinking of a different table where, although
duplicates aren't possible in the same way as above (a record that
matches the one being inserted is looked for first, if found no
insertion is done), some columns will be duplicated and it would require
more than one column to make them unique, in fact all three columns in
the table would be required to make a unique record.

In this case is the example I gave above correct or am I making a
mistake in my table design by doing it that way? ("that way" referring
to what I described in the paragraph above this one.)

 
Chris.