SQL unique varchar case sensitivity question

case-sensitive, mysql, sql, unique-constraint, varchar

Solution

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

Problem

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?

Original source