如何在Oracle中使用SQL确保多对多关系的一方的完全参与？

dbf7pr2w 于 2023-05-28 发布在 Oracle

关注(0)|答案(1)|浏览(126)

我试图在Oracle数据库中使用SQL在其中一方强制执行多对多关系的限制，但不知道如何操作。
例如，我有一组可以用this entity-relationship diagram（或下面的ASCII版本）描述的表：

( id1 )---[ E1 ] ====== < R > ------ [ E2 ]---( id2 )

其中实体E1与实体E2具有关系R，并且E1中的每个条目必须与E2的 * 至少一个 * 条目相关。
这可以转化为表E1（id1）、R（id1，id2）和E2（id2），我想确保在任何给定点，E1中的每个值id1在R中至少出现一次。
我已经成功地在插入新的E1条目时执行了这个规则，方法是将R上的FOREIGN KEY id1 REFERENCES E1(id1)约束设为DEFERRABLE，创建一个触发器BEFORE INSERT ON E1，如果:new.id1在R中不存在，则触发器BEFORE INSERT ON E1失败，然后总是在R中插入一个id1 = x的新条目，然后在E1中添加一个id1 = x的新条目。
然而，我找不到一种方法在R中删除条目时强制执行此限制，因为触发器无法检查R中是否存在另一个具有已删除条目的id1的条目，因为表正在发生变化。
这是我尝试使用的SQL触发器定义，但失败了，因为“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

来源：https://stackoverflow.com/questions/76336486/how-to-ensure-total-participation-on-one-side-of-a-many-to-many-relation-using-s

1条答案

按热度按时间

y1aodyip1#

一种可能的解决方案是使用复合触发器，该触发器允许对同一事件执行多个触发器操作。建议如下：使用BEFORE STATEMENT触发器创建一个临时表，该表包含R中至少有一个相关E2条目的所有E1条目的ID。然后使用FOR EACH ROW触发器检查删除的条目是否是临时表中与其对应的E1条目唯一相关的条目。如果是，则引发异常以阻止删除。例如：

CREATE OR REPLACE TRIGGER trig_E1StillHasAnE2_compound
FOR DELETE ON R
COMPOUND TRIGGER
  -- Create a temporary table to store the IDs of all E1 entries that have related E2 entries in R
  TYPE e1_ids_t IS TABLE OF R.id1%TYPE INDEX BY PLS_INTEGER;
  e1_ids e1_ids_t;
  
  BEFORE STATEMENT IS
  BEGIN
    e1_ids.DELETE;
    SELECT id1 BULK COLLECT INTO e1_ids
    FROM (SELECT DISTINCT id1 FROM R);
  END BEFORE STATEMENT;
  
  FOR EACH ROW IS
    -- Check if the deleted entry is the only related entry for its corresponding E1 entry in the temporary table
    e1_has_other_e2 BOOLEAN := FALSE;
  BEGIN
    IF e1_ids.EXISTS(:OLD.id1) THEN
      FOR i IN e1_ids.FIRST..e1_ids.LAST LOOP
        IF e1_ids(i) != :OLD.id1 AND R.id1 = e1_ids(i) THEN
          e1_has_other_e2 := TRUE;
          EXIT;
        END IF;
      END LOOP;
      IF NOT e1_has_other_e2 THEN
        RAISE_APPLICATION_ERROR(-20011, 'Deletion of the only relation of an E1 to E2s');
      END IF;
    END IF;
  END FOR EACH ROW;
END trig_E1StillHasAnE2_compound;
/

使用复合触发器克服了“表突变”错误。
相关id1值的临时表避免了直接查询被触发器修改的同一表。
临时表还允许检查每个已删除行的其他相关条目的存在，而不需要多次查询或批量操作。
但是，请注意，维护临时表“e1_ids”会在触发器执行期间增加一些开销，特别是在表R很大且经常修改的情况下。并发删除可能会导致争用问题。

赞(0）回复(0）举报 2023-05-28

我来回答

如何在Oracle中使用SQL确保多对多关系的一方的完全参与？

1条答案

相关问题

热门标签

最新问答