normalize_foreign_key_name.sql

DELIMITER ;;

DROP PROCEDURE IF EXISTS `normalize_foreign_key_name`;;
CREATE PROCEDURE `normalize_foreign_key_name`(IN `param_database_name` varchar(100) CHARACTER SET 'ascii', IN `param_table_name` varchar(100) CHARACTER SET 'ascii', IN `param_column_name` varchar(100) CHARACTER SET 'ascii')
	BEGIN

		DECLARE var_constraint_name_current VARCHAR(100);
		DECLARE var_constraint_name_new VARCHAR(100);
		DECLARE var_constraint_target_table VARCHAR(100);
		DECLARE var_constraint_target_column VARCHAR(100);

		SET var_constraint_name_current := (
			SELECT `constraint_name`
			FROM `information_schema`.`key_column_usage`
			WHERE `constraint_schema` = param_database_name AND `table_name` = param_table_name AND `column_name` = param_column_name
		);

		SET var_constraint_target_table := (
			SELECT `referenced_table_name`
			FROM `information_schema`.`key_column_usage`
			WHERE `constraint_schema` = param_database_name AND `table_name` = param_table_name AND `column_name` = param_column_name
		);

		SET var_constraint_target_column := (
			SELECT `referenced_column_name`
			FROM `information_schema`.`key_column_usage`
			WHERE `constraint_schema` = param_database_name AND `table_name` = param_table_name AND `column_name` = param_column_name
		);

		SET var_constraint_name_new := CONCAT('fk_', param_table_name, '_', var_constraint_target_table, '_', param_column_name);

		IF var_constraint_name_new != var_constraint_name_current THEN
			SET @query := CONCAT('
				ALTER TABLE `', param_table_name, '`
				DROP FOREIGN KEY `', var_constraint_name_current, '`,
				ADD CONSTRAINT `', var_constraint_name_new, '`
					FOREIGN KEY (`', param_column_name, '`)
					REFERENCES `', var_constraint_target_table, '` (`', var_constraint_target_column, '`);
			');

			PREPARE `stmt` FROM @query;
			EXECUTE `stmt`;
			DEALLOCATE PREPARE `stmt`;
		END IF;

	END;;

DELIMITER ;