And for some reason, MySQL seems to be hit with these things more than most software and database software products. And although I like working with MySQL, having done so for many years by now, there are certain aspects of it that just annoys the h*ll out of me. Things such as:
- We have feature A, yes. And we we also support feature B. Great. But you cannot use feature A and B at the same time.
- Oh, yeah, we support feature C, but really it is so slow that we recommend against using it.
- Sure we support feature D, but the implementation really sucks.
- Oh, storage engine E is sure supported, but the quality is so bad that we have disabled it completely.
- On platform F, we don't really support feature G, as there is a limitation H on that platform. A limitation that was removed from that platform sometime during the Nixon administration, but all the same.
- Feature I has been replaced by feature J, so using I will not be supported in the near future, but J still lacks the full functionality of I.
- Oh, yes, you can sure do that! You can use either feature K or feature L, which have overlapping functionality and none is really feature complete in and of itself.
- Our development team is hard at work at A) NOT getting rid of these annoyances and B) adding more of them.
Nope. No cigar. Dropping an index is nearly instantaneous and doesn't require rebuilding the table, dropping the FK does. (I just tried this in 5.6, just to make a point. Same thing there). But have I not forgotten something, should I not be able to issue a:
And then drop the FK and then the index? Nope, that actually seems to make things worse and seems to confuse MySQL a lot. If you do this, you may drop the index, without the FK, and in a sense things now works, as although the FK is there, it isn't checked! Even if you set FOREIGN_KEY_CHECKS back to 1! This really makes no sense at all to me!
Playing with FOREIGN_KEY_CHECKS and droping FKs and indexes in MySQL 5.6 seems to confuse the h*ll out of MySQL also. I might one day get down to create a reproducible case here.
Anyway, why can't you drop an FK without rewriting the table? When the index can be dropped without rewriting the table? And don't tell me not to use FKs, if a feature is implemented it really should work, else remove it. And if I am not to use FKs with MySQL (in almost any other RDBMS they work just fine), then why were they implemented? No, FKs are useful, not always and sometimes they are too limiting for performance, but in many cases, they are all OK and really should work.
As Thomas Watson of IBM fame put it: THINK!