CODEWITHSUNDEEP
phpmysqlsqlperformance
Celeritas
Celeritas

Reputation: 15063

Requiring a unique e-mail address

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.

Upvotes: 0

Views: 1606

Answers (4)

Jakub Kania
Jakub Kania

Reputation: 16487

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.

Upvotes: 0

cheeyos
cheeyos

Reputation: 681

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, [email protected] and [email protected] 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.

Upvotes: 1

Marc B
Marc B

Reputation: 360762

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.

Upvotes: 1

mti2935
mti2935

Reputation: 12027

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.

Upvotes: 1

Related Questions