Skip to content

Instantly share code, notes, and snippets.

@TravisBernard

TravisBernard/dropFkIfExists.sql

Created September 4, 2020 15:14
Download ZIP

Revisions

  1. Travis Bernard created this gist Sep 4, 2020.
    32 changes: 32 additions & 0 deletions dropFkIfExists.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,32 @@
    -- Derived from https://stackoverflow.com/a/34545062/2992570

    DROP PROCEDURE IF EXISTS dropForeignKeyIfExists;

    delimiter ///
    create procedure dropForeignKeyIfExists(IN tableName VARCHAR(64), IN constraintName VARCHAR(64))
    begin
    IF EXISTS(
    SELECT * FROM information_schema.table_constraints
    WHERE
    table_schema = DATABASE() AND
    table_name = tableName AND
    constraint_name = constraintName AND
    constraint_type = 'FOREIGN KEY')
    THEN
    SET @query = CONCAT('ALTER TABLE ', tableName, ' DROP FOREIGN KEY ', constraintName, ';');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END IF;
    end///

    delimiter ;

    CALL dropForeignKeyIfExists('RMSSiteDetails', 'RMSSiteDetails_ibfk_1');
    CALL dropForeignKeyIfExists('RMSDestination', 'RMSDestination_ibfk_1');
    CALL dropForeignKeyIfExists('RMSRequestedOptions', 'RMSRequestedOptions_ibfk_1');
    CALL dropForeignKeyIfExists('RMSCart', 'RMSCart_ibfk_1');
    CALL dropForeignKeyIfExists('RMSCustomer', 'RMSCustomer_ibfk_1');
    CALL dropForeignKeyIfExists('Item', 'Item_ibfk_2');

    DROP PROCEDURE IF EXISTS dropForeignKeyIfExists;