Referential Integrity with Virtual Columns

During ODTUG, a couple of weeks ago, Tom Kyte did a presentation on the “Top 10 11 things about Oracle 11gR1″. The presentation can be downloaded from asktom.oracle.com. He started by saying the Oracle 11g Release 2 can be expected the end of this calender year.
One of the things he showed was a way to clean up your model using Virtual Columns, a great new feature of the Oracle 11g database.

Use Virtual Columns to Clean up your Model

Virtual Columns are introduced in Oracle 11gR1 and are very usefull. A virtual column feels just like a “real” column but with a major difference. They are based on an expression: a SQL function or a User Defined Function. Just to show you a quick example, here is the definition of the infamous EMP table:

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Adding a virtual column to show an employees total compensation can be done like:

SQL> alter table emp
  2  add (income as (sal + nvl (comm, 0)))
  3  /


Table altered.

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 INCOME                                             NUMBER

SQL>

The Income column is a virtual column, based on the expression

sal + nvl (comm, 0)

The total compensation for an employee will always be calculated based on this expression.
I already knew that these virtual columns could be indexed and that it is possble to put a check constraint on them.

What Tom Kyte also showed was a way to use them to clean up your datamodel.
Suppose that you have a table that looks like this:

create table messed_up
(id number primary key
,val varchar2(30)
,composite_fk varchar2(10)
);

And you have another table that looks like this:

create table comp
(id number primary key
,str varchar2(30)
);

Fill these tables with some data:

insert into messed_up
values (1, 'x', 'a1')
/
insert into comp
values (1, 'This')
/

The COMPOSITE_FK column from the MESSED_UP table should have a foreign key relation with the COMP table. But not the whole column, just the numeric part of the column… Of course this is not the ideal way of doing this, it’s just an example. I called this table MESSED_UP for a reason…
Creating a foreign key which incorporates a function is not allowed:

alter table messed_up
add constraint mfk foreign key (to_number (substr (composite_fk, 2, 1)) references comp

Unfortunately that is not allowed. When you try to declare the foreign key without using a function, it fails because of datatype incompatibilty. And it just wouldn’t make sense anyway.

alter table messed_up
add constraint mfk foreign key (composite_fk) references comp
/
ORA-02267: column type incompatible with referenced column type

Instead of creating your own referrential integrity, *bad idea*, you can use Virtual Columns to implement this foreign key relation. First of all: create a virtual column which extracts the appropriate value from the COMPOSITE_FK column:

alter table messed_up
add (fk as (to_number (substr (composite_fk, 2,1))))
/

Next put the foreign key relation in place:

alter table messed_up
add constraint mfk foreign key (fk) references comp
/

And there you have it. I know this example is real dodgy, but you get the idea. It would have been a lot better to not have this datamodel to begin with, but you don’t always get that choice.