如何在Oracle中用SQL强制多对多关系一侧的完全参与约束

实体E1与E2通过关联表R构成多对多关系，要求每个E1的记录必须至少关联一个E2记录——即E1表中的每个id1值，必须在关联表R中存在至少一条对应记录。对应的表结构为：

• E1(id1)：主键为id1
• E2(id2)：主键为id2
• R(id1, id2)：联合主键，id1为引用E1(id1)的外键，id2为引用E2(id2)的外键

已实现的插入约束方案 #

将R表上的外键约束FOREIGN KEY (id1) REFERENCES E1(id1)设置为DEFERRABLE，并创建BEFORE INSERT ON E1触发器，检查待插入的:new.id1是否已存在于R表中，若不存在则抛出错误。操作时需先向R表插入该id1对应的关联记录，再插入E1表的记录，以此保证新E1记录满足完全参与约束。

删除约束遇到的问题 #

尝试创建行级触发器限制删除R表记录时，若删除后某个id1在R中无剩余记录则阻止操作，但触发了变异表错误（table tiposDoRestaurante is mutating）。失败的触发器代码如下：

create or replace trigger trig_E1StillHasAnE2 after delete on R
for each row
  declare numRelated int;
begin
  select count(id1) into numRelated
  from R
  where id1 = :old.id1
  group by id1;

  if numRelated = 0 then
    Raise_Application_Error(-20011, 'Deletion of the only relation of an E1 to E2s');
  end if;
end;
/

可行的解决方案：使用复合触发器 #

Oracle的复合触发器可以结合行级与语句级逻辑，避免直接查询正在修改的表导致变异表错误。实现思路是：先在行级触发器中收集所有被删除记录的id1，再在语句级触发器中批量验证这些id1在R表中的剩余关联数：

create or replace trigger trig_enforce_E1_full_participation
for delete on R
compound trigger
  -- 定义集合存储删除操作涉及的id1
  type t_id1_list is table of R.id1%type;
  v_id1_list t_id1_list := t_id1_list();

  -- 行级阶段：收集被删除记录的id1
  after each row is
  begin
    v_id1_list.extend();
    v_id1_list(v_id1_list.last) := :old.id1;
  end after each row;

  -- 语句级阶段：批量验证收集到的id1的剩余关联数
  after statement is
    v_remaining_count number;
  begin
    for i in v_id1_list.first .. v_id1_list.last loop
      select count(*) into v_remaining_count
      from R
      where id1 = v_id1_list(i);

      if v_remaining_count = 0 then
        raise_application_error(-20011, '无法删除E1的最后一条关联记录，E1必须至少关联一个E2');
      end if;
    end loop;
  end after statement;
end trig_enforce_E1_full_participation;
/

方案说明 #

1. 行级阶段仅收集需要检查的id1，不查询正在修改的R表，规避变异表问题；
2. 语句级阶段在整个删除操作完成后执行，此时R表状态稳定，可安全查询剩余关联数；
3. 支持批量删除操作，所有涉及的id1都会被逐一验证；
4. 若需限制E1表的删除操作，可额外创建BEFORE DELETE ON E1触发器，检查该id1在R中是否存在记录（根据需求，E1记录必须至少关联一个E2，因此通常应阻止直接删除E1记录，除非先通过特殊逻辑解除所有关联，但这会违反完全参与约束）。

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