0 
0 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.
Thank you all for your comments. The suggestion done by Lucas and Nigel, I placed in a new post: http://technology.amis.nl/blog/?p=3325
A common case for using a virtual column in a foreign key would be to enforce “generic foreign keys” – for example to the well known Oracle Designer CG_REF_CODES table.
create table CG_REF_CODES
( DOMAIN varchar2(20) not null
, LOW_VALUE varchar2(30) not null
, DESCRIPTION varchar2(100)
, …
);
For each referencing column we can define a virtual column that will be a constant (containing the value to match to CG_REF_CODES.DOMAIN).
So now we should be able to create a valid foreign key and not be concerned that there may be multiple rows in CG_REF_CODES with the same VALUE but different DOMAIN.
(Sorry, can’t test this out as I don’t have 11g installed on the laptop)
Regards Nigel
Could a Foreign Key also link to a Virtual Column (on which a Unique constraint is based)? That would allow the creation of Foreign Keys from say INSURANCE_POLICY explicitly to CAR when the CAR records are in a more general table that contains VEHICLES (or MEANS_OF_TRANSPORTATION).
regards, Lucas
Maybe I misheard him, but I’m pretty sure he said something along these lines.
Hi Alex,
First of all, interesting stuff, as always.
You already wrote that the example was a bit dodgy, but could you give an example where you should/could actually use a foreign key created on a virtual column? Isn’t it a bit dangerous to make a referenence using concatenated (or changed in any matter) data?
A constraint like a unique key would be more applicable on a virtual column than a referential key…
Sound premature…”He started by saying the Oracle 11g Release 2 can be expected the end of this calender year. “