How many of you have happened, that you want to change or delete a column in a database table and it is not possible because the column is a foreign key, which is being used in a constraint? No matter if you are using Xampp, MySql Workbench, or any other tool, no matter what you do, the column is restricted and therefore cannot be changed. I’m sure this has happened to many, especially when you’re just starting out… Also to me 😉 That’s why I want to put here a very simple solution in case someone is suffering with this.
The solution is to disable the foreign key check using «SET FOREIGN_KEY_CHECKS«.
Imagine you have two tables, «client» and «person«, which are linked by an external key constraint fk_client_person_idx. These would be the steps to remove the constraint and be able to change the definition of the column:
1. SET FOREIGN_KEY_CHECKS = 0; 2. LOCK TABLES cliente WRITE, persona WRITE; 3. ALTER TABLE cliente DROP FOREIGN KEY fk_client_persona_idx; 4. SET FOREIGN_KEY_CHECKS = 1;
In step one we deactivate the restrictions. Then in step 2, we lock the tables and then in step 3, we just remove the constraint from the column. Finally in step 4, we activate the constraints again. That’s it!
But please note that this is not a good practice in production environments, but rather a tool to be used during the development time. However, if you need to do it in production, please don’t forget to make a backup first 😉