Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.
You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates
ALTER statements that will fail when a table holds a multiple column foreign key value. The
SELECT statement would look like this when capturing all foreign key values in a MySQL Server:
SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '[Read more...]