Changing a column collation

Question

Hey there, I have a database already set up. I am trying to change the collation to case sensitive on my username column so it restricts login parameters to what they signed up with.

However I keep getting this: #1025 - Error on rename of './yebutno_ybn/#sql-76dc_8581dc' to './yebutno_ybn/user' (errno: 150)

there is foreign key constraints due to related tables....

any ideas? this will save me a lot of hassle with the php side of things!

Thanks, Stefan

Answer 1

I think you can turn off foreign key constraints by using something like:

SET FOREIGN_KEY_CHECKS = 0;

After your done adjusting, then you can enable them back. However i would recommend making a copy of the db and trying this on the copy first.

Answer 2

In addition to disabling FOREIGN_KEY_CHECKS, you could just drop the foreign key, do you stuff, recreate it. Probably not a good idea if rebuilding the index might take a real long time.

Be aware, changing collation might orphan some rows. STEfan will no longer be related to stefan...so you might want to investigate this to see if you will have any occurrences, and decide what you will do about it, before you proceed.