Register and ask your own question!
Using foreign_key_checks=0 with rebuild_constraints
My team is trying to figure out how to ALTER a large table in production which has a couple large related tables (via fk constraints)
We have done some testing with the rebuild_constraints option and found that during the rebuild constraints portion of the migration that the table locking would not be acceptable for running in our production environment
We are also aware of the drop_swap option which should allow us to get around this but we are nervous of course about the warning that failures to rename the table could mean you lose the table.
One option we have found that appears to sidestep this is to pass --set-vars foreign_key_checks=0 to the command. As we understand it this would mean:
- No guarantee of data consistency (as with the drop_swap approach)
- However no risk of being stuck in limbo if the rename fails
Just curious to see if anyone has experience with this type of approach or can see better alternatives? I've poked around on the forums and online and have not seen references to using foreign_key_checks=0 along with rebuild_constraints.
Happy to provide more info / context if required and very much appreciate any experience / insight the community here might have!