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

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.