Mysql 5.7 : Procedure to add a Constraint only if it does not exists

Viewed 40

I've made a procedure that should check if a constraint exits, and if it does not, add the specified constraint:

DROP PROCEDURE IF EXISTS ADD_FK_IF_NOT_EXISTS;
CREATE PROCEDURE ADD_FK_IF_NOT_EXISTS(
    IN tablename CHAR(255),IN fkname CHAR(255),IN fieldname CHAR(255),
    IN tableref CHAR(255),IN fieldref CHAR(255),IN ondelete BOOLEAN)
BEGIN
IF( ( SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE table_name = tablename AND table_schema = DATABASE() AND constraint_name = fkname
    ) <= 0 )
THEN
    SELECT 'WTF';
    IF( ondelete )
    THEN
        ALTER TABLE tablename ADD CONSTRAINT fkname
        FOREIGN KEY (fieldname) REFERENCES tableref (fieldref)
        ON DELETE CASCADE;
    ELSE
        ALTER TABLE tablename ADD CONSTRAINT fkname
        FOREIGN KEY (fieldname) REFERENCES tableref (fieldref);
    END IF;
END IF;
END;

How I'm using it:

CALL ADD_FK_IF_NOT_EXISTS("accessory_order", "FK_7F5003388D9F6D38", 
    "order_id","order", "order_id", false)

This works fine BUT if I remove the SELECT 'WTF'; line, it does not. Why this behavior?

0 Answers
Related