Business Rule: Only use Active Records

The Business Rule that needed to implemented:

Only "active" records can be used in other tables.

Before Oracle 11g, one way to implement this Business Rule was to Materialized View with a Check Constraint defined on it. However In Oracle 11g you can implement this Business Rule a lot simpler by using a Virtual Column and a Foreign Key.

First let set up the tables that we are going to use in this example:

create table lookup
(id number primary key
,code varchar2(10)
,value varchar2(100)
,ind_active varchar2(1)
);

alter table lookup
add constraint lookup_ind_chk check (ind_active in ('Y', 'N'))
/ 

This table has the ind_active column which dictates whether the record in this table may be used in other tables. "Y" indicates that we can use it in other tables, "N" indicates that you can not use it (anymore).

Next we need another table to reference our Lookup Table:

create table t
(id number primary key
,lu_id number references lookup
); 

Together with some data in both tables:

insert into lookup values (1, 'One', 'First Value', 'Y')
/
insert into lookup values (2, 'Two', 'Second Value', 'Y')
/
insert into t values (1, 1)
/
insert into t values (2, 2)
/ 

Now to implement the Business Rule, using the Materialized View method. Because the rule needs to be checked whenever someone wants to use a record from the Lookup table, we want to validate the rule as soon as possible. To have the Materialized View refresh when a COMMIT is issued, we need Materialized View Logs on both base tables:

create materialized view log on t with sequence, primary key including new values
/
create materialized view log on lookup with sequence, primary key including new values
/ 

The Materialized View definition will be

create materialized view t_lookup_mv
build immediate
refresh force on commit
as
select l.ind_active
from t
  , lookup l
where l.id = t.lu_id
/ 

This way we will get a record in the Materialized View whenever the T-table uses a record in our Lookup Table. The final thing we need is a Check Constraint on this Materialized View, because we only want Lookup records with the active_ind is “Y”:

alter materialized view t_lookup_mv
add constraint active_chk check (ind_active = 'Y');

Just to check that the Materialized View does what it is supposed to do:

update lookup
set ind_active = 'N'
where rownum <= 1
/
commit;

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (ALEX.ACTIVE_CHK) violated 

That seems to work. But as you can see, you need quite a lot of code to implement such a simple rule. The same Business Rule can be implemented in Oracle 11g a lot simpler, with less code.

Virtual Columns

This is the way we're going to implement the rule:

1. Add a Virtual Column to the Lookup table
2. Create a Foreign Key referencing the Virtual Column

And that's all there is to it. Let's take a look at the code. First of all the Virtual Column.

Virtual Columns are a new feature of the Oracle 11gR1 database. Using this feature you can create an extra column in the table that is based on an expression.

active_fk as (case when ind_active = 'Y' then id end) unique 

The values in this Virtual Column are only shown when the IND_ACTIVE column has the value of "Y". The value that is shown is the primary key of the base table. Querying the LOOKUP table, reveals the content of the ACTIVE_FK Virtual Column:

SQL> select *
2    from lookup
3  /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         Y          2

Changing the IND_ACTIVE column to "N" will remove the value from the ACTIVE_FK column:

SQL> update lookup
2 set ind_active = 'N'
3 where id = 2
4 /

1 row updated.

SQL> select *
2 from lookup
3 /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         N

SQL> rollback;

Because we are going to use the Virtual Column as referenced by a Foreign Key we are going to make it UNIQUE as well.

Now for the table which uses the records from our Lookup Table:

create table t
(id number primary key
,lu_id number
);
alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
/

The foreign key is this case is not on the primary key, but on the Virtual Column instead.

Let's add some data to the table and try it out.

insert into t values (1, 1);
insert into t values (2, 2);

update lookup
set ind_active = 'N'
where rownum <= 1
/
update lookup
 * ERROR at line 1: ORA-02292: integrity constraint (ALEX.T_LOOKUP_FK) violated - child record found

And there you have it. It's not possible to use deactivated records anymore.

Check when you COMMIT?

Note the difference though, the Materialized View method is checked when you commit your transaction. The Virtual Column method checks it immediately. If you want the Virtual Column to have similar to the Materialized View method, change the foreign key:

alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
deferrable initially deferred
/

Note the last sentence, deferrable initially deferred, this tells Oracle to not validate the constraint immediately but to postpone it until the transaction is ended.