...

View Full Version : MySQL VarChar Primary Key upper case and lower case



jfreak53
12-17-2011, 06:59 PM
I am in the middle of copying an old DB from a system on MSSQL to a new system on MySQL DB type.

Their old tables had a column as primary key that was 25 char varchar column. It works fine but I noticed there are 5 duplicate rows base on the fact that in the old MSSQL they had all caps on one and all smalls on the other. In MySQL it doesn't understand this is seems, and it marks it as duplicate. Is this true, is there a way around it? Thanks.

Old Pedant
12-17-2011, 09:29 PM
Sure...you just have to tell MySQL that the field in question *IS* case sensitive.

One easy way to do it:


CREATE TABLE tablename (
fieldname VARCHAR(25) BINARY PRIMARY KEY,
other ...,
fields ...
);

Adding the keyword BINARY to the VARCHAR type cause it to become case sensitive.

Incidentally, you *DID* have to do the equivalent when you used SQL Server. I forget you specify case sensitivity in SQL Server, but it is also case insensitive by default. (In fact, the ANSI standard for SQL says VARCHAR and CHAR are case insensitive by default. The standard doesn't even provide a way of specifying that a field is case sensitive but leaves that up to each DB as an option in implementation.)

jfreak53
12-17-2011, 10:01 PM
AHA, thanks Ped! Didn't know that one, I didn't setup the original SQL DB so I didn't know. Learn somethin new everyday :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum