How to ensure total participation on one side of a many-to-many relation using SQL in Oracle?

Question

I'm trying to enforce a restriction of a many-to-many relation with total participation on one of the sides using SQL in an Oracle database but can't figure out how.

For example, I have a set of tables that can be described by this entity-relationship diagram (or the ASCII version below):

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

Where the entities E1 have a relation R to the entities E2, and every entry in E1 has to be related to at least one entry of E2.
This translates to the tables E1(id1), R(id1, id2), and E2(id2), and I want to make sure that at any given point, every value id1 in E1, also appears as id1 in R at least once.

I've successfully managed to enforce this rule during insertions of new E1 entries by making the FOREIGN KEY id1 REFERENCES E1(id1) constraint on R be DEFERRABLE, creating a trigger BEFORE INSERT ON E1 that fails if :new.id1 doesn't exist in R, then always inserting a new entry with id1 = x in R before adding a new entry with id1 = x in E1.

However, I can't find a way to enforce this restriction upon deletion of an entry in R, as triggers wouldn't be able to check for the existence of another entry with the id1 of the removed entry in R since the table is mutating.
This is the SQL trigger definition I tried to use but fails because "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;
/

score 0 · Answer 1 · answered May 26 '23 at 01:00

A possible solution is to use a compound trigger that allows multiple trigger actions for the same event. Suggested here: use a BEFORE STATEMENT trigger to create a temporary table that contains the IDs of all E1 entries that have at least one related E2 entry in R. Then use a FOR EACH ROW trigger to check if the deleted entry is the only related entry for its corresponding E1 entry in the temporary table. If so, raise an exception to prevent the deletion. e.g:

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;
/

Using a compound trigger overcomes the "table mutating" error.
The temporary table of relevant id1 values avoids direct querying of the same table being modified by the trigger.
The temporary table also allows checks for the presence of other related entries of each deleted row without requiring multiple queries or bulk operations.
However, note that maintaining the temporary table "e1_ids" adds some overhead during the execution of the trigger, especially if table R is large and frequently modified. Concurrent deletions might cause contention issues.

How to ensure total participation on one side of a many-to-many relation using SQL in Oracle?

1 Answers1