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?