SQL unique varchar case sensitivity question

Question

I'm trying to populate a SQL table with a list of words. The table itself it pretty simple:

CREATE TABLE WORDS(
  ID BIGINT AUTO_INCREMENT, 
  WORD VARCHAR(128) NOT NULL UNIQUE, 
  PRIMARY KEY(ID)
);

The problem I'm running into is this: when I do the following inserts back to back

INSERT INTO WORDS(WORD) VALUES('Seth');
INSERT INTO WORDS(WORD) VALUES('seth');

The second insert fails with a constraint violation ("Duplicate entry 'seth' for key 'WORD'").

How can I get the UNIQUE constraint on WORD to be case sensitive?

score 132 · Accepted Answer · edited Apr 18 '18 at 18:42

132

Looks like mysql is case insensitive by default:

You probably need to create the column with a case sensitive collation (e.g. utf8_bin):

CREATE TABLE WORDS (
    ID BIGINT AUTO_INCREMENT, 
    WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE, 
    PRIMARY KEY(ID)
);

edited Apr 18 '18 at 18:42

Community

1
1

answered Jun 23 '11 at 02:58

Bill Brasky

2,614
2
19
20

31

That did it. Thanks. For anyone finding this question later, I had to use the 'utf8_bin' collation because mysql didn't have 'utf8_cs'. – Seth Jun 23 '11 at 03:29
Thanks @Seth, that was helpful along with Bill's info, utf8_bin applied to my situation as well – groovenectar May 17 '12 at 16:50
1

#1273 - Unknown collation: 'utf8_cs' – feedc0de Jul 17 '14 at 19:41
5

Huge disappointment with mysql :-( "Seth" and "seth" are NOT same strings! Thanks for helping out. – Josef Sábl Aug 14 '14 at 10:54
Use **utf8_bin** in case of **utf8_cs** – Lauris Kuznecovs Apr 11 '16 at 09:23
I have collation set as `utf8_bin`. And still varchar(50) `FFAB213D-810C-74BD-1B34-E93FC473CF6A` is the same as `ffab213D-810C-74BD-1B34-E93FC473CF6A` (MariaDB 10) – stamster Apr 18 '17 at 14:18
1

For utf8mb4, you can use `SET utf8mb4 COLLATE utf8mb4_bin` instead. – Xhua Apr 26 '19 at 08:03
Many Thanks man, my clients were about to file case on me. Use this https://jol.ink – Surajit Biswas Oct 13 '20 at 14:18

score 12 · Answer 2 · answered Jun 01 '15 at 16:14

By default MySQL ignores differences in case and trailing spaces on varchar.

If you need it to be case sensitive, you can alter the table to be varchar(...) binary.

Use show create table to better understand how MySQL converts this to full notation.

If you need to pay attention to trailing spaces as well as be case sensitive, use varbinary instead of varchar.

score 0 · Answer 3 · answered Dec 01 '20 at 09:44

0

Try this:

ALTER TABLE WORDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

answered Dec 01 '20 at 09:44

Khubaib Raza

543
6
10

score -4 · Answer 4 · answered Oct 06 '17 at 02:25

-4

I made my UNIQUE key varchar(1000). It worked.

After some trial and error, I found anything greater than or equal to 1100 varchar would fail.

To clarify I did not try between 1001 to 1099.

Hope this helps.

answered Oct 06 '17 at 02:25

suleman

66
6

SQL unique varchar case sensitivity question

4 Answers4

Linked