Reputation: 87
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
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
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.
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
Reputation: 3663
You need to drop Unique constraint:
ALTER TABLE foobar DROP INDEX constraint_name;
Upvotes: -1