CODEWITHSUNDEEP
sqlmysqlmysql-error-1062
Earl
Earl

Reputation:

Cant make field unique!

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`)

Upvotes: 0

Views: 582

Answers (5)

Toto
Toto

Reputation: 2430

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

Remember: NULL != ''

To find them:

SELECT id FROM users WHERE email= '';

Upvotes: 1

CS.
CS.

Reputation: 1895

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562358

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.

Upvotes: 2

mmmmmm
mmmmmm

Reputation: 32681

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 '' )

Upvotes: 0

SquareCog
SquareCog

Reputation: 19666

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

Upvotes: 4

Related Questions