1

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

2
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.

  • As designed, this trigger does not prevent an UPDATE that reassigns a team member. Granted it was not in the original description, but it would be a requirement. – Jim D Sep 24 '20 at 17:07
  • @JimD this trigger does not prevent an UPDATE that reassigns a team member That is true. And this is done in strict accordance with the task set by the author: "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". No words about UPDATE. Nevertheless it is enough to alter one word (and trigger name) and double the code (check both OLD and NEW teams) - and update check is realized too. – Akina Sep 24 '20 at 17:45
  • @Akina Thanks for your answer however I have a quick question: lets say there's a team with id: 1 and member_limit:1 now if the following two queries executed at the same time INSERT INTO team_member VALUES (DEFAULT, 1, 600);, INSERT INTO team_member VALUES (DEFAULT, 1, 700);. did the trigger will also executed at the same time so I'll have members out of the member_limit range. – Amjed Omar Sep 24 '20 at 18:02
  • @AmjedOmar In theory it is possible that the number of players will be over-limited in such case under default server settings. This depends on current transaction level settings (each query is separate transaction). – Akina Sep 24 '20 at 18:11
2

This should be achievable with a simple query. We'll have to query team and team_member to get the required information, so might as well do everything at once:

INSERT INTO team_member
SELECT
  team.team_id
 ,<the user's id>
FROM
  team team
INNER JOIN
  team_member team_member
    ON team_member.team_id = team.id
WHERE
  team.id = <team id>
GROUP BY
  team.team_id
HAVING
  COUNT(*) < team.member_limit

When you wrap this in a stored procedure you can raise a flag if ROW_COUNT() = 0.

A couple of other notes:

  1. The Id column of team_member is unnecessary, does not guarantee uniqueness for the intended purpose. Your primary key is (team_id,member_id).
  2. As a naming convention, just using Id is poor form. Always use the context so the relation is clear, e.g. User_Id, Team_Id.
-1

Cross row constraints are tricky and the implementation depends on the API you present. You may be able to adapt this technique https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4233459000346171405 from AskTOM.

As recommended in the comments, here is a solution that works but has its limitations. When dealing with cross row constraints, my recommendation is to carefully evaluate any solution with a complete set of requirements. The question, as posted, only mentioned the INSERT limitation; however, not acting on UPDATEs and DELETEs would cause problems.

I opted for an AFTER INSERT/UPDATE/DELETE because I generally prefer to modify related tables in an AFTER trigger. That may not be the right solution for your specific use case, so caveat emptor.

The lack of deferrable constraint checking in MySQL makes this type of cross row constraint difficult to make robust. Assuming MySQL is a hard requirement, the assignment of users to teams should probably be managed by stored procedures.

CREATE TABLE team (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT, 
  `name`          VARCHAR(255) NOT NULL,
  `member_count`  INT, 
  `member_limit`  INT NOT NULL
);

CREATE TABLE user (
  `id`            BIGINT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE 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`)
);

ALTER TABLE team
  ADD CONSTRAINT enforce_team_member_limit
    CHECK ((member_count >= 0) AND (member_count <= member_limit))
;
    
DELIMITER //
CREATE OR REPLACE TRIGGER tr_ai_update_team_size
AFTER INSERT
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) + 1
      WHERE id=NEW.team_id;
END;
//
CREATE OR REPLACE TRIGGER tr_au_update_time_size
AFTER UPDATE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) + 1
      WHERE id=NEW.team_id;
    UPDATE team SET member_count = NVL(member_count,0) - 1
      WHERE id=OLD.team_id;
END;
//
CREATE OR REPLACE TRIGGER tr_ad_update_team_size
AFTER DELETE
ON team_member
FOR EACH ROW
BEGIN
    UPDATE team SET member_count = NVL(member_count,0) - 1
      WHERE id=OLD.team_id;
END;
//
DELIMITER ;

Some test statements

INSERT INTO team VALUES (1, 'Team 1', null, 3);
INSERT INTO team VALUES (2, 'Team 2', null, 2);

INSERT INTO user VALUES (1);
INSERT INTO user VALUES (2);
INSERT INTO user VALUES (3);
INSERT INTO user VALUES (4);

COMMIT;

INSERT INTO team_member VALUES (1, 1, 1);
INSERT INTO team_member VALUES (2, 1, 2);
INSERT INTO team_member VALUES (3, 1, 3);

INSERT INTO team_member VALUES (4, 1, 4);

DELETE FROM team_member where team_id=1 and member_id=3;
INSERT INTO team_member VALUES (4, 1, 4);

Here is a mockup on dbfiddle.

Pros:

  • Avoids having a SELECT clause in the trigger (avoiding a full index scan or shudder full table scan plus the attendant concerns about transaction isolation)

  • The check constraint enforces the limitation in the team table, thus preventing buffoonery

  • The member_count column could make some routine queries more efficient (e.g. how many people are on a team).

Cons:

  • An update statement that swaps users between teams that are full will fail because MySQL does not support deferrable constraints.

  • The introduction of the member_count column.

I am not a big fan of having columns that are essentially derived from something else stored in the database, but I will make the trade-off for performance or constraint checking reasons. I tend to advocate for constraints because, in my experience, making the database enforce data quality prevents buffoonery by applications or users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.