SQL unique varchar case sensitivity question
MysqlSqlCase SensitiveVarcharUnique ConstraintMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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)
);
Solution 2 - Mysql
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
.
Solution 3 - Mysql
Try this:
ALTER TABLE WORDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Solution 4 - Mysql
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.