CODEWITHSUNDEEP
mysqlsql
Ali Dahaghin
Ali Dahaghin

Reputation: 87

How to Update a Unique Column in Mysql to uppercase?

in mysql 5.7 :

I want to execute UPDATE foobar SET foo = 'Bar' WHERE foo = 'bar'

foo is unique

but I get #1062 - Duplicate entry 'Bar' for key 'foobar_foo_unique'

what should i do ?

Upvotes: 0

Views: 1377

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

It sounds like you want a case-sensitive collation for the column (or perhaps for the database, but that is another matter).

You can set that when you define the table:

CREATE TABLE foobar (
    foo VARCHAR(80) COLLATE utf8_bin
);

You can define the column to be unique in the table or create a separate index/constraint:

CREATE TABLE foobar (
    foo VARCHAR(80) COLLATE utf8_bin UNIQUE
);

Note that this has the happy side effect that comparisons on the column are case sensitive as well, so where foo = 'bar' and where foo = 'Bar' are different.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

Seems you have a functional unique index on foo column such as

CREATE UNIQUE INDEX foobar_foo_unique
ON foobar( (ascii(foo)) );

Consider this :

mysql> CREATE TABLE foobar( foo VARCHAR(80) );

mysql> CREATE UNIQUE INDEX foobar_foo_unique ON foobar( (ascii(foo)) );

mysql> INSERT INTO foobar VALUES('Bar');

mysql> INSERT INTO foobar VALUES('bar');

mysql> INSERT INTO foobar VALUES('Bar');  

Duplicate entry ....

mysql> SELECT * FROM foobar;

+------+
| foo  |
+------+
| Bar  |
| bar  |
+------+

If you want to have case insensitive uniqueness, convert this constraint to a standard unique index after manipulating the data properly.

Demo

Another case : You may have whitespaces wrapping around(even between letters). In this case, you can think that you encounter the duplication problem. Consider this Demo also.

Upvotes: 1

Soumendra Mishra
Soumendra Mishra

Reputation: 3663

You need to drop Unique constraint:

ALTER TABLE foobar DROP INDEX constraint_name;

Upvotes: -1

Related Questions