如何在SQLite中强制群组-子群组-目标的共存关联约束？

首先得先修正初始Schema里的一个小问题：group是SQL的关键字，直接用它当表名会引发语法冲突，所以我把表名改成了groups。接下来我们通过优化表结构配合数据库触发器，就能实现你要求的两个核心约束。

第一步：调整后的完整Schema #

-- 修正表名避免关键字冲突
CREATE TABLE groups(
 group_id INTEGER NOT NULL,
 PRIMARY KEY(group_id)
);

CREATE TABLE target(
 target_id INTEGER NOT NULL,
 group_id INTEGER NOT NULL,
 index INTEGER NOT NULL,
 input BLOB NOT NULL,
 output BLOB NOT NULL,
 PRIMARY KEY(target_id),
 FOREIGN KEY(group_id) REFERENCES groups(group_id),
 UNIQUE(group_id, index)
);

CREATE TABLE subgroup(
 subgroup_id INTEGER NOT NULL,
 group_id INTEGER NOT NULL,
 index INTEGER NOT NULL,
 PRIMARY KEY(subgroup_id),
 FOREIGN KEY(group_id) REFERENCES groups(group_id),
 UNIQUE(group_id, index)
);

CREATE TABLE subgroup_target(
 subgroup_id INTEGER NOT NULL,
 target_id INTEGER NOT NULL,
 output BLOB NOT NULL,
 PRIMARY KEY(subgroup_id, target_id),
 FOREIGN KEY(subgroup_id) REFERENCES subgroup(subgroup_id),
 FOREIGN KEY(target_id) REFERENCES target(target_id)
);

第二步：用触发器实现核心约束 #

约束1：每个subgroup的subgroup_target必须完全镜像所属group的target集合 #

我们需要两个触发器来保障这个规则：一个在插入subgroup_target时做合法性检查，另一个在删除target时自动清理关联数据。

插入subgroup_target时的合法性检查

CREATE TRIGGER validate_subgroup_target_insert
BEFORE INSERT ON subgroup_target
FOR EACH ROW
BEGIN
  DECLARE group_target_total INTEGER;
  DECLARE subgroup_target_current INTEGER;
  
  -- 获取当前subgroup所属group的target总数
  SELECT COUNT(*) INTO group_target_total
  FROM target
  WHERE group_id = (SELECT group_id FROM subgroup WHERE subgroup_id = NEW.subgroup_id);
  
  -- 获取当前subgroup已有的subgroup_target数量
  SELECT COUNT(*) INTO subgroup_target_current
  FROM subgroup_target
  WHERE subgroup_id = NEW.subgroup_id;
  
  -- 已达到总数则阻止插入
  IF subgroup_target_current >= group_target_total THEN
    RAISE(ABORT, "无法添加subgroup_target：该subgroup已完成对应group的所有target映射");
  END IF;
  
  -- 额外检查：确保target和subgroup属于同一个group，避免跨组关联
  IF (SELECT group_id FROM target WHERE target_id = NEW.target_id) != (SELECT group_id FROM subgroup WHERE subgroup_id = NEW.subgroup_id) THEN
    RAISE(ABORT, "subgroup_target关联的target与subgroup不属于同一个group");
  END IF;
END;

删除target时自动清理关联的subgroup_target

CREATE TRIGGER clean_subgroup_target_on_target_delete
AFTER DELETE ON target
FOR EACH ROW
BEGIN
  DELETE FROM subgroup_target WHERE target_id = OLD.target_id;
END;

约束2：未完成所有subgroup的映射时，禁止为group新增target #

这个约束需要在插入target前检查：当前group下的所有subgroup是否都已经完成了现有target集合的映射，只有全部完成才能新增target。

CREATE TRIGGER prevent_new_target_in_incomplete_group
BEFORE INSERT ON target
FOR EACH ROW
BEGIN
  DECLARE group_subgroup_count INTEGER;
  DECLARE completed_subgroup_count INTEGER;
  
  -- 获取当前group的subgroup总数
  SELECT COUNT(*) INTO group_subgroup_count
  FROM subgroup
  WHERE group_id = NEW.group_id;
  
  -- 如果group下没有subgroup，直接允许插入新target
  IF group_subgroup_count = 0 THEN
    RETURN;
  END IF;
  
  -- 统计当前group中，已完成全部target映射的subgroup数量
  SELECT COUNT(DISTINCT st.subgroup_id) INTO completed_subgroup_count
  FROM subgroup_target st
  JOIN subgroup s ON st.subgroup_id = s.subgroup_id
  WHERE s.group_id = NEW.group_id
  GROUP BY st.subgroup_id
  HAVING COUNT(st.target_id) = (SELECT COUNT(*) FROM target WHERE group_id = NEW.group_id);
  
  -- 存在未完成映射的subgroup则阻止插入
  IF completed_subgroup_count != group_subgroup_count THEN
    RAISE(ABORT, "无法新增target：该group下部分subgroup未完成现有target的映射");
  END IF;
END;

逻辑说明 #

1. 跨组关联拦截：确保subgroup_target只能关联同组的target，避免数据混乱
2. 数量匹配校验：插入subgroup_target时限制总数，保证最终能完全镜像对应group的target集合
3. 新增target的前置检查：只有当group下所有subgroup都完成了当前target的映射，才能新增target，避免出现映射滞后的情况

注意事项 #

• 以上触发器基于SQLite语法编写，如果使用PostgreSQL、MySQL等其他数据库，需要调整触发器的语法细节（比如异常抛出方式、变量声明逻辑）
• 如果需要支持修改target的group_id，还需要额外添加触发器来检查和清理关联数据
• 触发器会逐行执行，批量操作时也能保证每一步都符合约束

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