如何在Elixir Ecto中限制n:n多对多关联的记录数量上限

方案说明 #

你之前尝试的行级CHECK约束无法生效是PostgreSQL的固有机制限制：行级CHECK约束仅能校验当前插入/更新行自身的字段值，不支持跨表查询、其他行聚合统计类的判断逻辑，因此无法直接实现人数统计类的限制。
以下是完全基于Ecto标准接口实现的方案，所有数据库操作都通过Ecto迁移、Ecto API完成，不需要手动直连数据库执行命令，符合开发规范要求：

1. 核心业务层拦截：事务+排他行锁 #

所有玩家加入游戏的操作必须包裹在数据库事务中，插入关联记录前先对目标游戏记录加排他行锁，阻塞同一游戏的并发加入请求，统计当前玩家数确认未达上限后再执行插入，能拦截绝大多数超员场景。
代码实现：

@doc "添加玩家到指定游戏，超出人数上限时返回错误"
def add_player_to_game(game_id, player_id) do
  Repo.transaction(fn ->
    # 加FOR UPDATE排他锁，事务结束前其他针对该游戏记录的加锁请求会排队，避免并发统计偏差
    game =
      Game
      |> Repo.get!(game_id)
      |> Repo.lock("FOR UPDATE")

    current_count =
      GamesPlayer
      |> where(game_id: ^game_id)
      |> Repo.aggregate(:count)

    if current_count >= game.player_limit do
      Repo.rollback(:player_limit_exceeded)
    else
      %GamesPlayer{}
      |> GamesPlayer.changeset(%{game_id: game_id, player_id: player_id})
      |> Repo.insert!()
    end
  end)
end

2. 数据库层兜底：Ecto迁移实现触发器校验 #

为了避免代码bug、手动修改数据等极端情况产生超员数据，可以通过Ecto迁移创建触发器做强制兜底，所有逻辑都随迁移文件统一版本管理，通过mix ecto.migrate执行，不属于违规直接操作数据库。
迁移文件代码：

defmodule YourApp.Repo.Migrations.AddGamePlayerLimitConstraint do
  use Ecto.Migration

  def up do
    execute """
    CREATE OR REPLACE FUNCTION check_game_player_limit()
    RETURNS TRIGGER AS $$
    BEGIN
      IF (
        SELECT COUNT(*) FROM games_players WHERE game_id = NEW.game_id
      ) > (
        SELECT player_limit FROM games WHERE id = NEW.game_id
      ) THEN
        RAISE EXCEPTION 'Game reached maximum player limit';
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """

    execute """
    CREATE TRIGGER enforce_game_player_limit
    BEFORE INSERT ON games_players
    FOR EACH ROW
    EXECUTE FUNCTION check_game_player_limit();
    """
  end

  def down do
    execute "DROP TRIGGER IF EXISTS enforce_game_player_limit ON games_players;"
    execute "DROP FUNCTION IF EXISTS check_game_player_limit();"
  end
end

优化建议 #

• 为games_players表的game_id字段创建索引，可大幅提升玩家数统计查询的性能
• 新增游戏人数上限更新逻辑时，同步校验新的上限值不能小于当前已加入的玩家数量
• 在接口层统一捕获:player_limit_exceeded错误，返回对应业务提示给前端

内容的提问来源于stack exchange，提问作者GuiMendel