Using Scripts to Ensure Data Integrity in Oracle

Question

Is it bad practice to use triggers or scripts to maintain data integrity that Oracle is not designed to enforce, or is this a sign I'm modeling my data in a poor way?

From responses to a previous post (Implementing User Defined Fields), I have decided that I want to move forward designing with a mix of Class and Concrete Inheritance. I want one base class for all SAMPLE then a concrete table for each unique set of attributes.

While I can enforce that each concrete table has a parent entry in SAMPLE by making SAMPLE.sample_id the primary key with a foreign key constraint. However, I do not know how to enforce that a SAMPLE entry has exactly one child since the child entry could be in any number of tables.

How can I enforce this? If the solution is INSERT, UPDATE, and DELETE triggers, is this considered bad-practice?

Answer 1

Say your "main" table is called TableA and it's primary key is called "ID". Create your second table, say TableB, also with a primary key named "ID". Now define TableB(ID) as a foreign key to TableA(A).

Having TableB(ID) as a foreign key means it can only have a value if it exists in TableA(ID), and having it be the primary key means that it can't have a value more than once.

Answer 2

Well, it depends on the kind of integrity you are looking for.

The database is designed for referential integrity. So if that's what you are looking for, use the database's structures. Don't roll your own.

If you are trying to maintain other sorts of integrity (like MACs for rows) then triggers are completely acceptable.

Answer 3

If the data are only ever modified by your own stored procedures, then I wouldn't bother with checking this constraint.

In fact, now that I think of it, there's no need to check in the INSERT case. You're INSERT'ing both a SAMPLE and a CONCRETE_1 row in the same transaction. There cannot be a CONCRETE_2 row with the same PK, as the SAMPLE row did not previously exist.

Answer 4

Triggers cannot be relied on for enforcing integrity.

Tom Kyte explains why: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:599808600346047256

The problem is that a transaction cannot see what other uncommited transactions are doing.

To take a simple example, transactions A and B can both intend to insert the same value "X" into a table, and can both check that such a value does not already exist. Transaction A commits after transaction B checks for existing values of "X". Transaction B finds no X's, so it also inserts its own X and commits. Now the uniqueness requirement is defeated.

The only way to avoid this is for the entire process of checking for an existing value, inserting it, and commiting the insert to be serialised.

Add to that the problem that a trigger cannot see the contents of the table it fires against because it is mutating ...

Answer 5

After commenting in a few of the answers here, I feel compelled to show an example of how triggers can be used to enforce rules that go beyond the basic RI provided by RDBMS.

From our own system, we have several tables which all have foreign keys pointing to the same table ("MasterTable"). Over time, the rows in these tables will be deleted; once the last child row has been deleted removed, we want to delete the parent row and take some action. We enforced this rule by creating a column "ChildCount" on the parent table, which indicates the number of rows that refer to this one (in other words, a reference count).

In the insert triggers of all the various child tables, we have code that looks like this:

      SELECT ChildCount
        INTO numrows
        FROM MasterTable mt
       WHERE :new.MasterTableId = MasterTable.MasterTableId
         FOR UPDATE;

      UPDATE MasterTable
         SET ChildCount = ChildCount + 1
       WHERE :new.MasterTableId = MasterTable.MasterTableId;

In the delete triggers of the child tables, we have this:

      SELECT ChildCount
        INTO numrows
        FROM MasterTable
       WHERE :old.MasterTableId = MasterTable.MasterTableId
         FOR UPDATE;

      DELETE MasterTable
       WHERE ChildCount = 1
         AND :old.MasterTableId = MasterTable.MasterTableId;

    IF Sql%RowCount = 0 THEN
         UPDATE MasterTable
            SET ChildCount = ChildCount - 1
          WHERE :old.MasterTableId = MasterTable.MasterTableId;
    END IF;

The update triggers contain both bits of code.

The key bit in this logic is using a separate select statement with the FOR UPDATE clause, instead of just updating the column with a single statement. It ensures that simultaneous transactions will be serialized properly.

Since MasterTable already has declared delete-cascade rules to all the child tables, the code above will cause ORA-04091 (mutating table) errors when it executes in the context of deleteing a MasterTable row that has existing children, so these statements are done in the context of an EXCEPTION block that catches and ignores this error.

Finally, the code above is generated for us automatically from the CASE tool we use for data modeling (ERWin). ERWin allows you to create "user defined properties" (UDP), and it has a macro language that can be used to generate virtually any code you need based on your schema, so all we need to do to enable this feature is add the ChildCount column to the appropriate parent table and set the UDP for "Ref-Counted Relationship" to true.

As I pointed out in comments above, triggers can't be used to replace declared RI completely, since you can't use FOR UPDATE to make delete cascade rules work properly. But it's great for supplemental rules like this.

Note: This code has been in production for 11 years now - it was designed back when we were still using Oracle 7. If someone has a more modern way of doing this using built-in Oracle features, I'd be interested in hearing about it.

Answer 6

I think you can solve this by using a materialized view that is a union all of TABLEA, TABLEB and TABLEC + groub by on master table id. You have to create a materialized view logs to make this a fast refreshable materialize view. And you have add a check constraint that throws an error when there is more than row in the materialized view per master table id.

Rob van Wijk explains here http://rwijk.blogspot.com/2009/07/fast-refreshable-materialized-view.html a lot about fast refresible mv's. Rob van Wijk is often present here at stackoverflow too.

Here you can read on the use of check constraints on materialized views: http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints

Using fast refresizable mv's means that the integrity check is done during committing, not during the inserting or updateting of data.

I'm a very tired I can't test it myself and I can't provide a real example.

edit1: Here is the example:

It works when you create a fast refresh mv with a check constraint and a unique function based index.

First we create the tables:

SQL> create table mastertable (id number(10) not null primary key);

SQL> create table tablea
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tableb
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tablec
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

Then we create the mv logs:

SQL> create materialized view log on tablea with rowid (master_id) 
     including new values;

SQL> create materialized view log on tableb with rowid (master_id) 
     including new values;

SQL> create materialized view log on tablec with rowid (master_id) 
     including new values;

The mv (the umarker column is really needed!):

SQL> create materialized view table_abc
     refresh fast with rowid on commit
     as
     select master_id,count(*) master_count, 'A' umarker
     from   tablea
     group by master_id
     union all
     select master_id,count(*) master_count, 'B' umarker
     from   tableb
     group by master_id
     union all
     select master_id,count(*) master_count, 'C' umarker
     from   tablec
     group by master_id
     /

Now we add a check constraint to this mv to ensure that you can't insert twice in the same detail table per master_id:

SQL> alter table table_abc add check (master_count in (0,1) );

And we add a unique function based index to this mv to ensure that you can't insert in table a and table b with the same master_id:

SQL> create unique index table_abc_ufbi1 on table_abc
     (case when master_count = 1 then master_id else null end);

Test 1 (the happy path):

SQL> insert into mastertable values (1);

1 rij is aangemaakt.

SQL> insert into tablea values (1,1);

1 rij is aangemaakt.

SQL> commit;

Commit is voltooid.

Test 2 (one insert in table a and one insert in table b with same master_id)

SQL> insert into mastertable values (2);

1 rij is aangemaakt.

SQL> insert into tablea values (2,2);

1 rij is aangemaakt.

SQL> insert into tableb values (3,2);

1 rij is aangemaakt.

SQL> commit; commit * FOUT in regel 1: .ORA-12008: Fout in pad voor vernieuwen van snapshot. ORA-00001: Schending van UNIQUE-beperking (TESTT.TABLE_ABC_UFBI1).

test 3 (insert in table a twice with same master_id)

SQL> insert into mastertable values (3);

1 rij is aangemaakt.

SQL> insert into tablea values (4,3);

1 rij is aangemaakt.

SQL> insert into tablea values (5,3);

1 rij is aangemaakt.

SQL> commit; commit * FOUT in regel 1: .ORA-12008: Fout in pad voor vernieuwen van snapshot. ORA-02290: CHECK-beperking (TESTT.SYS_C0015406) is geschonden.

Answer 7

The best way to ensure that a parent record cannot be inserted without a child is to use the INSERT ALL syntax. This allows us to insert records into multiple tables it the same statement.

    INSERT ALL
        INTO parent
             (pk_col, val1, val2)
        INTO child1 
             (pk_col, val3, val4)
    SELECT some_seq.nextval as pk_col
           , val1
           , val2
           , val3
           , val4
    FROM where_ever

The table WHERE_EVER can be a staging table (perhaps an external one). In your case it would DUAL with the VAL columns being parameters from your stored procedure's signature.

You will not be able to prevent a rogue developer writing code which inserts a PARENT record without a CHILD*n* record. Similarly you cannot stop the insertion of a record into CHILD2 using the primary key of a PARENT which already has a CHILD1 record. For that I'm afraid you need code reviews.

Answer 8

I think in this particular case, changing your db model, and not creating scripts or triggers, is the answer.

You mentioned in an earlier comment:

Well, I have TABLE then TABLE_A, TABLE_B, and TABLE_C. Each entry in TABLE must have EXACTLY ONE entry in EITHER TABLE_A, TABLE_B, or TABLE_C. The FK constraint on A, B, C only does half. A Table A and Table B could then both have the same parent (which I don't want).

I would suggest this:

1. Create an isABC column in TABLE which specifies the type as A,B or C. Better still, create a new table with the PK (primary key) of A and the new column isABC (with the same PK and a FK on TABLE.PK).
2. Set up (PK, isABC) as a unique constraint.
3. Add (PK, isABC) columns to *TABLE_A*, *TABLE_B* and *TABLE_C*. Make it a FK (foreign key) constraint on the same columns in TABLE (or the new table).
4. On each of *TABLE_A*, *TABLE_B* and *TABLE_C*, set up a check constraint on column isABC that checks the value is "A", "B" and "C" respectively.

This design creates an additional isABC column in *TABLE_A*, *TABLE_B* and *TABLE_C* as the price of enforcing this constraint, but you get away from hairy implementations using scripts, triggers or procedures.