Cant make field unique!

Question

Can you not have more than 2 unique fields in a table or am i doing something wrong here?

I have 1 unique key for username and i want it for email too but i get

#1062 - Duplicate entry '' for key 'email'

alter table users
add unique (email)

Tbl:

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(40) NOT NULL,
`email` varchar(100) NOT NULL,
`registered` int(11) unsigned NOT NULL DEFAULT '0',
`is_active` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`)

Answer 1

It's telling you you already have non-unique entries in the email field, namely, the value '' (empty string)

Answer 2

You can have more than one unique field.

I think the error is complaining about the data in the email field. (I think you have more than one row with a value of '' )

Answer 3

You have the same value (i.e. '') in the email column on more than one row. That means you can't put a UNIQUE constraint on that column.

You could make the column nullable, then update the value to NULL where it's currently '', and then create a UNIQUE constraint on it, because UNIQUE permits nulls.

ALTER TABLE users MODIFY email VARCHAR(100);

UPDATE users SET email = NULL WHERE email = '';

ALTER TABLE users ADD UNIQUE KEY (email);

BTW, why do you have the value '' in the email column? That's not a valid email address.

Answer 4

No value is also considered unique, so two email rows with nothing are duplicate, if they are to be judged by a unique standard.

Answer 5

This means that (at least) 2 records have an empty email.

Remember: NULL != ''

To find them:

SELECT id FROM users WHERE email= '';