homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
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

 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!



 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)

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)

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)

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)

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);
insert your_table (email) values (null);
insert your_table (email) values (null);
insert your_table (email) values (null);
insert your_table (email) values (null);
insert your_table (email) values ('something');
insert your_table (email) values ('something');

Is that what you're after?


 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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved