Delete all foreign keys in mysql database

Question

I can see that there are some questions [1,2] that already ask for this, but where the solution didn't contain a complete SQL script to do this task. I have a situation where it would be very helpful to delete all foreign keys using SQL only. Currently I'm trying to solve this with a stored procedure and a cursor as follows:

-- No automatic commits:
DROP PROCEDURE IF EXISTS removeConstraints;
-- Magic to happen soon:
DELIMITER |
CREATE PROCEDURE removeConstraints()
BEGIN
  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;
  -- https://dev.mysql.com/doc/refman/5.0/en/cursors.html
  -- https://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql
  -- https://mariadb.com/kb/en/mariadb/cursor-overview/
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR SELECT CONCAT('ALTER TABLE ',TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
    FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
    WHERE REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO s;
    IF done THEN
      LEAVE read_loop;
    END IF;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  SET AUTOCOMMIT=1;
END |
DELIMITER ;
-- Do magic:
CALL removeConstraints();
-- Cleanup:
DROP PROCEDURE removeConstraints;

Sadly this produces the following error message:

ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE ' at line 8

---

With the input from Ravinder Reddy I've now updated the DECLARE parts right after the BEGIN to look like this:

CREATE PROCEDURE removeConstraints()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR 
          SELECT DISTINCT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
          WHERE TABLE_SCHEMA=DATABASE()
          AND REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

but when I try to execute the procedure I still get an error:

  MariaDB [v4]> CALL removeConstraints();
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

I've also tried to use a different SELECT statement like:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
WHERE TABLE_SCHEMA=DATABASE()
AND CONSTRAINT_NAME != 'PRIMARY'
AND CONSTRAINT_NAME IS NOT NULL
AND TABLE_NAME IS NOT NULL;

…but it didn't help.

---

I've got it working now by changing the code so that the CONCAT happens later.

DROP PROCEDURE IF EXISTS removeConstraints;
-- Magic to happen soon:
DELIMITER |
CREATE PROCEDURE removeConstraints()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tName VARCHAR(64);
  DECLARE cName VARCHAR(64);
  DECLARE cur CURSOR FOR 
          SELECT DISTINCT TABLE_NAME, CONSTRAINT_NAME
          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
          WHERE TABLE_SCHEMA=DATABASE()
          AND REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO tName, cName;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('ALTER TABLE ',tName,' DROP FOREIGN KEY ',cName,';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  SET AUTOCOMMIT=1;
END |
DELIMITER ;
-- Do magic:
CALL removeConstraints();
-- Cleanup:
DROP PROCEDURE removeConstraints;

Answer 1

All DECLARE statements must be on top in a BEGIN - END block.

And all other statements should follow them.

In your code, you have SET statements defined before DECLARE statements.
Move those statements to below of DECLARE statements.

Sample:

  -- https://dev.mysql.com/doc/refman/5.0/en/cursors.html
  -- https://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql
  -- https://mariadb.com/kb/en/mariadb/cursor-overview/
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR 
          SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
            FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
           WHERE REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

Document Reference:

DECLARE Syntax

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations