11

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in complex joins and if an email changes I'd have to change all the foreign keys which requires a lot of effort.

But if my users table requires every user to have an email address and each of those email address should be unique, will adding a unique index on the email column suffice? Because afaik unique fields allow null values, whereas I require every user to have an email address, not allowing null values. Is there something I'm missing here? Or I'm suppose to make email column unique and make sure during data validation on the server that user does enter an email address so that every user has one?

  • 3
    What happens when a user changes his email address - as they will e.g. changing job – Mark Dec 29 '14 at 16:59
  • 1
    String comparisons are not just slower, strings also tend to be bigger than say, an integer, and hence you can fit fewer on a page in memory, pushing up your logical reads for queries. – Nameless One Dec 29 '14 at 21:57
7

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key.

A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally used as a primary key (I never really understood why one of the c.k. is concidered "better" than the others, but that's another story), and the remaining c.k becomes unique constraints.

A unique constraint can be used the same way as a primary key can. Consider:

create table A ( x ... not null
               , y ... not null
               , z ... not null
               ,     unique (x)
               ,     primary key (y,z) );

create table B ( x ...
               ,   ...
               ,     foreign key (x) references A (x) );

create table C ( y ...
               , z ...
               ,   ...
               ,     foreign key (y, z) references A (y, z) );  

B references the unique constraint and C references the primary key constraint.

NOT NULL is yet another kind of constraint. In your case you can enforce this for email without declaring it unique.

The next aspect of your post concerns the stability of a key, a key should be stable (but that doesn't mean it can never change, it does not have to be immutable). Some DBMS implements ON UPDATE CASCADE that can be of help for such operation, still if the key is distributed around your model it will be a pain updating it.

In your case I would probably choose another candidate key as the primary key, and declare email to be NOT NULL and UNIQUE.

  • 1
    In SQL Server you can reference a unique index as a FK. – Martin Smith Dec 29 '14 at 7:12
  • 1
    I don't have access to sql so I cannot check for myself, does it implicitly create a unique constraint when you create a unique index? – Lennart Dec 29 '14 at 7:22
  • 1
    No. A unique constraint is treated slightly differently and has some additional metadata and additional restrictions compared to a unique index but SQL Server allows either to be used in an FK. – Martin Smith Dec 29 '14 at 7:25
  • 1
    That's a bit odd then, indexes aren't even mentioned in the sql standard whereas keys are a central part of it. Anyhow, thanks for the information. – Lennart Dec 29 '14 at 7:30
  • It is worth noting that if there are a lot of records foreign keyed to your email, it can take a fair bit of time to update all those records when the update cascades. – cimmanon Dec 29 '14 at 19:38
6

Yes having a unique index on the EmailAddress column should be ok. The only problem would be if someone gave up the email address after signing up for your service but didn't tell you, then whoever the owner of the email address tries to sign up. But that's a pretty rare edge case.

As to if a Unique Index allows null values that'll depend on your database platform. Oracle does, SQL Server allows a single NULL value. You can solve this by making the column not allow NULL values, then building the unique index on it.

  • 1
    That's not true about SQL server. You can create indices with where clauses that, for example, allow you to exclude NULL values from the index. – Kirk Woll Dec 29 '14 at 14:38
  • 1
    The statement SQL Server allows a single NULL value is still true. It does not say there is no way to get multiple NULL values. I think the answer-er was trying to keep the answer simple and not explain extra details (like filtered indexed). – Brandon Dec 29 '14 at 16:03
  • 1
    Yes I could have dove down the rabbit whole of filtered indexes but a simple question usually needs a simple answer. Without a database platform and version I keep my answers generic. – mrdenny Dec 29 '14 at 17:36
2

Having the unique index on EmailAddress is fine.

As you have already stated that there is validation in your application for having Email Address as required field, I would say as the other validation would be from database is not accept a user with out an Email address and prevent duplicate entry as well and these validation will be imposed with this Unique Index.

As stated in other answer for SQL Server you need to make a column not allow null value before building unique indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.