SQL unique varchar case sensitivity question

user_195625 0
Viewed 56079

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?

unique-constraint varchar case-sensitive mysql sql
4 Answers

Try this:

ALTER TABLE WORDS CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
user_81111900

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.

user_43524920
Related