homepage Welcome to WebmasterWorld Guest from 54.211.138.180
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Unique index on column with null values
create unique index on colum with null values
Tourex




msg:4387995
 11:00 am on Nov 17, 2011 (gmt 0)

Hi Guys

I know the answer is going to be blindingly obvious, but for the life of me, it is escaping my tired old mind.

I need to store customer details in a MySQL database. Although primary access will be through a unique system-generated auto-incrementing key, customer access will be via the email address. However, the table contains records for which we have no email address. So, how can I create a unique key on the email field to prevent duplication when various records have null values in that field?

Any help will be much appreciated. Thanks!

 

arms




msg:4388121
 3:17 pm on Nov 17, 2011 (gmt 0)

By your own definition you can't have a unique key on a field with duplicate values (null or otherwise), but you could create another field with an autonumber and create a compound key with the email address field, this would then be uniqur but retain the original values in the email field

Tourex




msg:4388129
 3:42 pm on Nov 17, 2011 (gmt 0)

Hi Arms

Thanks - and I total agree and understand everything you say. The problem with the compound key is that it will not enable me to quickly/easily find an account by email address and prevent opening a 'duplicate' account if the email is already used in another account.

penders




msg:4388179
 4:57 pm on Nov 17, 2011 (gmt 0)

I can't see the benefit of creating a compound key?

I think you should just create an ordinary index (it doesn't have to be unique) on the email field and perform an additional query to check for duplicate email address as part of your validation. Is there an easier way?

rocknbil




msg:4388185
 5:12 pm on Nov 17, 2011 (gmt 0)

With a system already in place, I'd just do a lookup before inserting.

$err = check_existing($email);
if (! $err) {
// proceed as normal
}


function check_existing($email) {
if (empty($email)) { return "<li>email is empty</li>"; }
$error = null;
$query = "select id from table where email = '$email';
$result = mysql_query($query) or die("can't check for existing email");
if ($row = mysql_fetch_array($result)) {
$error = "<li>Sorry but the email address $email already exists in our system.</li>";
}
return $error;
}

Tourex




msg:4388210
 6:31 pm on Nov 17, 2011 (gmt 0)

Thanks all

Yes, I was hoping there might be a more elegant way than doing an extra lookup - but no problem and thanks for your input.

Jstanfield




msg:4388264
 9:09 pm on Nov 17, 2011 (gmt 0)

Unique indexes ignore nulls. Uniqueness is only enforced on non-nulls.


create unique index by_email on your_table(email);
OK
insert your_table (email) values (null);
OK
insert your_table (email) values (null);
OK
insert your_table (email) values (null);
OK
insert your_table (email) values (null);
OK
insert your_table (email) values ('something');
OK
insert your_table (email) values ('something');
FAIL, DUPLICATE


Is that what you're after?

Tourex




msg:4388503
 11:22 am on Nov 18, 2011 (gmt 0)

Thanks Jstanfield

You are spot on, of course. I was confusing primary keys with unique keys, hence the reason my early tests weren't giving the results I wanted. Problem solved! I can use the auto-incremented key as my primary, use a unique index on the email field as a precaution and then do a manual query before creating new accounts just as a pre-check.

Thanks for you help everyone.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved