Skip to content

Instantly share code, notes, and snippets.

@DrumRobot
Last active April 23, 2020 11:31
Show Gist options
  • Select an option

  • Save DrumRobot/c70bf09de9d7d693a06682039de0eca8 to your computer and use it in GitHub Desktop.

Select an option

Save DrumRobot/c70bf09de9d7d693a06682039de0eca8 to your computer and use it in GitHub Desktop.
MySQL modify foreign key procedure
DROP PROCEDURE IF EXISTS modify_fk;
DELIMITER $$
CREATE PROCEDURE modify_fk (IN $table TEXT, IN $constraint TEXT, IN $column TEXT, IN $references TEXT)
BEGIN
SET @st1 = CONCAT_WS(" ",
"ALTER TABLE", $table,
"DROP FOREIGN KEY", $constraint);
PREPARE ncv_st1 FROM @st1;
EXECUTE ncv_st1;
SET @st2 = CONCAT_WS(" ",
"ALTER TABLE", $table, "ADD CONSTRAINT", $constraint,
"FOREIGN KEY (", $column, ") REFERENCES", $references);
PREPARE ncv_st2 FROM @st2;
EXECUTE ncv_st2;
COMMIT;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment