0

A table in a MySQL database has a column for e-mail addresses. Ultimately the e-mail addresses are supposed to be unique and have valid formats. I'm having trouble deciding where the checking should be done and what checking is necessary.

Obviously SQL alone can't entirely validate an e-mail address but I was considering adding the NOT NULL constraint to prevent the submission of blank e-mail addresses. Since each e-mail address must be unique making the e-mail column a unique key seems reasonable, but just because a column is a unique key doesn't make it NOT NULL right? Since I'm probably going to be validating the e-mail address on the server using PHP I could just as well check to see if it's empty there.

A critical piece of information I'm making is does adding a unique key or a constraint make searches faster or slower?

For a column that holds e-mail addresses where there should be no duplicates and no empty strings/nulls etc. should it be made a unique key and/or given a NOT NULL constraint or something else?

I'm very novice with MySQL so code samples would be helpful. I've got phpMyAdmin if it's easier to work with.

For the unique I would use ALTER TABLE USER ADD UNIQUE INDEX(``e-mail``);
For the not null I would use ALTER TABLE user CHANGE ``e-mail`` varchar(254) NOT NULL;

Another idea I had was insert a row with a null e-mail address and then make the e-mail column unique so no other null e-mail addresses can be inserted.

Celeritas
  • 14,489
  • 36
  • 113
  • 194
  • You can validate emails with SQL (using custom constraints) and adding keys usually makes searches faster – Dany Khalife Jul 12 '13 at 19:11
  • A unique constraint communicates your intent better than a unique index. – Mike Sherrill 'Cat Recall' Jul 12 '13 at 19:17
  • @MikeSherrill'Catcall' what's the difference? According to [here](http://stackoverflow.com/questions/3245328/what-is-the-difference-between-unique-index-and-unique-key) the terms are synonymous. – Celeritas Jul 12 '13 at 19:31
  • 1
    Do not assume that your php app is to only way your database can be accessed. With that in mind, a unique constraint and making the field not null are good ideas. However, it's not enough. Your php code has to handle attempts to write data that you don't want. In other words, if I put in an email address that is already there, your web page should not crash. – Dan Bracuk Jul 12 '13 at 19:45
  • A unique constraint is a relational concept. A unique index is a physical concept--it has performance implications. MySQL is *not* a good source for understanding relational concepts. – Mike Sherrill 'Cat Recall' Jul 12 '13 at 20:34

4 Answers4

1

Adding a unique constraint will actually make searches faster, because it will index the table by this field. Based on your description of the problem, I think your alter table statements are correct.

mti2935
  • 11,465
  • 3
  • 29
  • 33
1

Fields with unique indexes can still allow nulls. nulls can never be equal to anything else, including themselves, so multiple nulls are not a violation of the uniqueness constraint. You can disallow nulls in the field by specifying it as NOT NULL, however.

A unique key is a normal field index, that simply doesn't allow multiple instances of a particular value. There will be a slight slowdown on insert/update so the key can be updated, but searches will be faster, because the index can (in some cases) be used to accelerate the search.

Marc B
  • 356,200
  • 43
  • 426
  • 500
1

The answers so far are good, and I would recommend using UNIQUE KEY and NOT NULL for your application. Using UNIQUE KEY may slow down INSERT or UPDATE, but it would certainly not slow down searches.

However, one thing you should consider is that just because you use UNIQUE KEY, it does not necessarily enforce unique e-mail addresses. As an example, abc@gmail.com and a.b.c@gmail.com represent the same e-mail. If you don't want to allow this, you should normalize e-mail addresses in PHP before sending them to your database.

cheeyos
  • 671
  • 4
  • 11
  • Could you elaborate on normalizing the e-mail addresses? Maybe it would be better to scrap `UNIQUE KEY` and `NOT NULL` and do everything in PHP. – Celeritas Jul 12 '13 at 19:29
  • By normalizing I meant removing periods in the user name part of the e-mail. I don't recommend scrapping `NOT NULL` and `UNIQUE KEY` from your database. Even if you can enforce everything at the front-end level, there are still many advantages to keep your schema the way it is meant to be. For instance, if your PHP has bugs in the validation process, it may cause inconsistencies in the database. Or when other people are developing other parts of the system, they might make unnecessary/false assumptions if they see that the schema does not enforce `UNIQUE KEY` or `NOT NULL`. – cheeyos Jul 12 '13 at 20:07
0

With MySQL you have to remember that unique index depends on the collation of your whole table (in other db you can make on upper() function). See this link: http://sqlfiddle.com/#!2/37386/1 Now, if you use utf8_general_ci insted of utf8_bin the index creation would fail.

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47