Mysql – How to not allow to insert a new record if count of current records exceeds a specified limit

foreign keylimitsMySQL

I have two tables as the following

team

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`member_limit` INT NOT NULL

team_member

`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`team_id` BIGINT NOT NULL,
`member_id` BIGINT NOT NULL,

CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`),
CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`)

My question is on MySQL

How to not allow to insert a new member into team_member when the inserted member's team_id reference to team that count of its current members on team_member exceeds its member_limit

Best Answer

CREATE TRIGGER tr_bi_check_limit
BEFORE INSERT
ON team_member
FOR EACH ROW
BEGIN
    IF ( SELECT COUNT(*)
         FROM team_member
         WHERE team_id = NEW.team_id ) > ( SELECT member_limit - 1
                                           FROM team
                                           WHERE id = NEW.team_id ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Team limit reached!';
    END IF;
END

fiddle with some explanations.

Related Question