Skip to content

Instantly share code, notes, and snippets.

@annejohnson
Last active March 9, 2023 00:59
Show Gist options
  • Save annejohnson/d9c35ef8a575771fac7d to your computer and use it in GitHub Desktop.
Save annejohnson/d9c35ef8a575771fac7d to your computer and use it in GitHub Desktop.
Conditionally drop a primary key from a table in MySQL (v. 5.6)
-- Temporarily change delimiter to $$
DELIMITER $$
-- Drop procedure if it exists
DROP PROCEDURE IF EXISTS drop_pk_if_exists $$
-- Begin create procedure
CREATE PROCEDURE drop_pk_if_exists () BEGIN
-- If my_table has a primary key
IF EXISTS(select * from `information_schema`.`table_constraints`
where `constraint_schema` = database()
and `constraint_type` = "PRIMARY KEY"
and `table_name` = "my_table") THEN
BEGIN
-- Drop the primary key
ALTER TABLE `my_table` DROP PRIMARY KEY;
END;
END IF;
END $$
-- Reset delimiter back to ';'
DELIMITER ;
-- Call procedure
CALL drop_pk_if_exists();
-- Drop procedure
DROP PROCEDURE drop_pk_if_exists;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment