My predecessor built our database with some "overloaded" child tables that are shared by multiple parents, using a "tabletype" column that specifies which parent table is the parent of a particular child record. Also, the parents and child are often joined using multiple columns which are not keys, or a compound key, or unique in any way. Multiple parents records can be related to multiple child records this way. Usually, SELECT DISTINCT or GROUP BY are used to eliminate duplicates in the results, in reports or forms. Apparently this is the way our data really works, and users are fine with it. I am not mandated to change this structure.
In one example, the child table has a "tabletype" column with one of three possible values (and currently no constraint to enforce them). It has a foreign key column to relate to the ID of one parent table (call it ParentA). This column is blank for records related to the other two parents. It has an identifying number (not unique) column (we will call it "IdentiNum") and a "BatchID" column, and joins with either of the other two parents using those two columns.
As you'd expect, Referential Integrity is not enforced, and probably can't be enforced with simple RI triggers and constraints. I'm an Access programmer, new to Oracle and PL/SQL. I can write code to enforce RI in the Access interface using VBA. That'll do no good if we replace this interface with one using APEX or another tool, as we plan to do. I want RI in the database where it belongs.
Here's what I think I need for this case:
- a constraint on tabletype allowing one of three values that specify which table contains a record's parent.
- a constraint on the child's ForeignKey column requiring its value to exist in the ID column of ParentA, unless it is null, which it might be.
- a delete trigger on ParentA which cascade-deletes related records in the child table, but still allows the child's ForeignKey to be nullable.
- a constraint on the child's IdentiNum and BatchID columns, requiring the values to exist (together) in either ParentB or ParentC, depending on the value of TableType.
- delete triggers on ParentB and ParentC which cascade-deletes related records in the child table, the relation determined by IdentiNum, BatchID, and TableType. However, when a ParentB or ParentC record is deleted, the procedure would have to check to make sure there were no other parent records with the same IdentiNum and BatchID values before deleting all related child records.
