Unique index on column with null values

Forum Moderators: open

Message Too Old, No Replies

Unique index on column with null values

create unique index on colum with null values

Tourex

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

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

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

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

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

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

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

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.