Dropping Foreign Key In Production Environment

I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, rebuilding indexes, etc.

You should disable anything related to such keys before dropping them

Using the variables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS in your session, run

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

Doing this in your session makes mysqld bypass referential checks in your session. You are still subject to the usual locks if anyone is accessing the tables whose constraint you are trying to discard. Indexes would be dropped quickly thereafter.

Your best approach would be to have a test server with the same amount of Production data. Run these two commands and perform the drop of the constraints in such a Staging server before doing this in Prod.

If you need to do this in Prod, schedule proper downtime.

Afterwords, do this

STEP 01 : Run the following

SET GLOBAL read_only = 1;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

STEP 02 : Drop all foreign keys

STEP 03 : Run the following

SET GLOBAL read_only = 0;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

STEP 04 : End Downtime

StackExchange Database Administrators Q#110723, answer score: 2