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