I have ISO 639-2 language codes (eng, fre, hin, etc.) in english as primary key in my master table. This column is foreign key in may other tables. Now my issue is even though my master have only lower case values, due to human error some values were added in other tables with language id in mixed cases. Even though there was foreign key, it didn't prevent it from happening.
Now this is first time I am working in MySQL, previously I worked on Oracle and it applies case sensitivity to keys. What should be done to have same functionality in MySQL?
Also what should be the column type?
Right now it is varchar(3). Should I convert it to something else? I am not going to use any like condition in any of the query, only = and in.
views:
30answers:
1
+2
A:
It happens because collation of collumn is case insensitive - something like latin1_swedish_ci change it to case sensitive collation - latin1_swedish_cs
ALTER TABLE t1 MODIFY col1 VARCHAR(3) CHARACTER SET latin1 COLLATE latin1_swedish_cs;
Naktibalda
2010-06-21 10:13:29
You might need to run queries of the form `UPDATE table SET column = LOWER(column)` first, so as to make the data lowercase.
Hammerite
2010-06-21 11:59:49
@Hammerite I would rather like to have this check at DB level than code level. As a programmer, right now I can control all inserts from my code, but couple of years down the line, some one else might just miss it in his code, and cause problem further.
Ashish Patil
2010-06-24 15:18:44
@Naktibalda Let me check your suggestion on my test DB, I hope this should solve problem. Do I need to alter master table only or all other referencing columns also? Another suggestion was to alter column to varchar binary. Will it help?
Ashish Patil
2010-06-24 15:19:37
You have to update data before altering table to fix invalid values.Alter all tables, charset must be the same.If all your records contains lowercase latin letters only, it doesn't matter is it a case sensitive or binary column.
Naktibalda
2010-06-24 15:54:45