Automatically remove foreign key dependencies with MySQL 5.0

Markus Popp's post (Foreign key dependencies) on how to print a nicely formatted list of table dependencies is just one example of how information_schema, stored routines and dynamic SQL in MySQL 5.0 not only ease the life of developers but of each and every DBA. Even if there are still a few obstacles in the way with current release 5.0.18 to realize all the DBA's dreams (see also my post on table checksum), we can still expand the example to automatically remove all dependencies.

Markus used all three of the above new tools, including dynamic SQL via PREPARE stmt_name FROM …. However there are two restrictions on dynamic SQL within stored routines that will often result in DBAs still using external scripting languages instead of pure SQL:

Markus' example that currently presents a list of table dependencies could easily be expanded to automatically remove them to be able to drop the referenced table. But to do that we need to loop over the result set of dependencies and thus use a CURSOR. Luckily, in that specific example we don't need to use a prepared statement, as the query itself is not dynamic, only its parameters are. So a simpler version of the exact same procedure looks like this:

delimiter //

CREATE PROCEDURE getDeps(pSchema VARCHAR(64), pTable VARCHAR(64))
  DETERMINISTIC
  READS SQL DATA
  SQL SECURITY INVOKER
BEGIN
  SELECT   CONCAT(REFERENCED_COLUMN_NAME,
                  ' -> (', TABLE_SCHEMA, '.', TABLE_NAME, ') ',
                  COLUMN_NAME
           ) AS `references`
  FROM     information_schema.KEY_COLUMN_USAGE
  WHERE    REFERENCED_TABLE_SCHEMA = pSchema
  AND      REFERENCED_TABLE_NAME = pTable
  ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
END //

From that we can construct a PROCEDURE delDeps that deletes all dependencies by stepping through the result set (something that's not yet possible for all problems that really need to make use of dynamic SQL):

delimiter //

CREATE PROCEDURE delDeps(pSchema VARCHAR(64), pTable VARCHAR(64))
  DETERMINISTIC
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
BEGIN
  DECLARE  done BOOLEAN DEFAULT FALSE;
  DECLARE  rSchema, rTable, rConstraint VARCHAR(64);
  
  DECLARE  dependencies CURSOR FOR
  SELECT   TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
  FROM     information_schema.KEY_COLUMN_USAGE
  WHERE    REFERENCED_TABLE_SCHEMA = pSchema
  AND      REFERENCED_TABLE_NAME = pTable;
  
  DECLARE  CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
  
  OPEN dependencies;
  dependency_loop: LOOP
       FETCH dependencies INTO rSchema, rTable, rConstraint;
       IF `done` THEN LEAVE dependency_loop; END IF;
       SET @delDeps_query := CONCAT('ALTER TABLE ', rSchema, '.', rTable,
                                    ' DROP FOREIGN KEY ', rConstraint);
       PREPARE delDeps_stmt FROM @delDeps_query;
       EXECUTE delDeps_stmt;
       DEALLOCATE PREPARE delDeps_stmt;
  END LOOP dependency_loop;
  CLOSE dependencies;

END //