3

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Stefan P
  • 1,013
  • 2
  • 18
  • 34
  • Just a quick warning of something to consider in case you haven't thought of it. If you make user name case sensitive, you create the possibility for someone to spoof Stefan by creating a user called stefan. – Disillusioned Jul 05 '12 at 11:33

2 Answers2

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.

Sabeen Malik
  • 10,816
  • 4
  • 33
  • 50
1

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.

goat
  • 31,486
  • 7
  • 73
  • 96
  • It's still not released to the public, so I'm stopping the problem before hand. I am going to make it so they can only login using the exact username so it's the same across the board when data is stored form sessions! How can I drop the foreign key temporarily? – Stefan P Apr 29 '10 at 02:30
  • ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; – goat Apr 29 '10 at 02:57