The query IF EXISTS not working for MySQL

Question

I have the following query I'm trying to run.

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'trading' AND table_name LIKE 'strategies')
THEN
ALTER TABLE `Strategies` DROP CONSTRAINT `assigned_equity_percent_not_over_100`
END IF;

I currently get a SYNTAX error however and I'm not sure why? This was the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES ' at line 1

As far as I can tell my syntax appears to be correct so I'm not sure what I'm missing. Any help is appreciated. I'm using the latest version of MySQL from the docker image. This is the version I'm running of MYSQL exactly Entrypoint script for MySQL Server 8.0.24-1debian10 started

Here is my attempt running it via phpmyadmin

I got the idea for my SQL query from this answer (Alter table if exists or create if doesn't), but doesn't seem to work?

Answer 1

The following code is my solution using sequelize and javascript but should apply to any other languages.

let sql_for_constraint = `DROP PROCEDURE IF EXISTS trading.create_constraint;
                    DELIMITER $$
                    CREATE PROCEDURE trading.create_constraint()
                    BEGIN
                        IF EXISTS (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = 'assigned_equity_percent_not_over_100')
                        THEN
                            ALTER TABLE Strategies DROP CONSTRAINT assigned_equity_percent_not_over_100;
                        END IF;
                        ALTER TABLE Strategies ADD CONSTRAINT assigned_equity_percent_not_over_100 CHECK (assigned_equity_percent <= 100 AND assigned_equity_percent>=0);
                    END$$
                    DELIMITER ;
                    CALL create_constraint();`;
                    //@JA - The code below cleans up the SQL so that it is API friendly to send to MySQL since delimiter is not supported via API
                    sql_for_constraint = sanitizer(sql_for_constraint);//@JA - This is defined from the helper.js file I created since this will be a common occurance.
                    await sequelize.query(sql_for_constraint, { raw:true,type: sequelize.QueryTypes.RAW });

                    let sql_for_trigger = `DROP PROCEDURE IF EXISTS trading.check_sum_strategy_assigned_equity;
                    DELIMITER $$
                    CREATE PROCEDURE trading.check_sum_strategy_assigned_equity(IN new_equity_percentage DECIMAL(5,2))
                    BEGIN
                        IF ((SELECT SUM(assigned_equity_percent) total FROM Strategies WHERE strategy_pack_id = strategy_pack_id)+new_equity_percentage)>100
                        THEN
                        SIGNAL SQLSTATE '45000' SET message_text = 'Can not exceed 100 for assigned equity percent for a strategy_pack_id';
                        END IF;
                    END$$
                    DELIMITER ;
                    DROP TRIGGER IF EXISTS sum_strat_ass_eq;
                    CREATE TRIGGER sum_strat_ass_eq
                    BEFORE INSERT ON Strategies
                    FOR EACH ROW
                    CALL trading.check_sum_strategy_assigned_equity(NEW.assigned_equity_percent);`
                    sql_for_trigger = sanitizer(sql_for_trigger);//@JA - This is defined from the helper.js file I created since this will be a common occurance.
                    await sequelize.query(sql_for_trigger, { raw:true,type: sequelize.QueryTypes.RAW });

What the code above does is adds a check constraint or drops if it already exists and then I add a trigger which makes sure the sums of values do not exceed 100 for the table using the same tactic.

Something to note is that MySQL client does NOT SUPPORt delimiter, so to fix this I made a little helper function. Depending on your language you would just have to do the equivalent to remove these from the SQL.

//@JA - This function sanitizes custom SQL so that any delimiter code is removed which only works via client or phpmyadmin.
var sanitize_for_mysql = function(sql) {
    sql = sql.replace(/([$][$])/gm, ";");
    sql = sql.replace(/DELIMITER ;/gm, "");
    return sql;
};

module.exports = sanitize_for_mysql;