how to use a trigger to validate input data
DELIMITER $$
CREATE TRIGGER example_before_insert_allow_only_one_active
BEFORE INSERT ON example_tbl FOR EACH ROW
BEGIN
IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl
WHERE active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
END IF;
END;
$$
CREATE TRIGGER example_before_update_allow_only_one_active
BEFORE UPDATE ON example_tbl FOR EACH ROW
BEGIN
IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl
WHERE id<>NEW.id AND active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
END IF;
END;
$$