Welcome to WebmasterWorld Guest from 54.145.144.101

Forum Moderators: open

Message Too Old, No Replies

Unique index on column with null values

create unique index on colum with null values

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

10+ Year Member



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!
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
3:42 pm on Nov 17, 2011 (gmt 0)

10+ Year Member



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.
4:57 pm on Nov 17, 2011 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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?
5:12 pm on Nov 17, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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;
}
6:31 pm on Nov 17, 2011 (gmt 0)

10+ Year Member



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.
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?
11:22 am on Nov 18, 2011 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month