Referential Integrity with Virtual Columns, the Sequel

The comments on the blogpost on Referential Integrity with Virtual Columns lead to this entry.

As a recap: In the above mentioned blog post I showed how you can use a Virtual Column – a new feature of the Oracle 11g Database – to disect a composite value and use part of it in a foreign key constraint.

Nigel Thomas and Lucas Jellema suggested it the other way around. Use a Virutal Column to create a foreign key to a “generic” table. For this example I will use Nigel’s suggestion using CG_REF_CODES as a generic lookup table.

In Oracle Designer CG_REF_CODES is used to store domains. In this table allowable values are stored per domain. For example, you can have a domain called “Martial Status” and this domain can have the values “Single”, “Married” and “Divorced”. When a new value for this domain is needed, simply add this value to the CG_REF_CODES table and presto, now this new value can be used.

Until the introduction of Virtual Columns, it was not possible – as far as I know – to place a foreign key constraint to the CG_REF_CODES table. In Oracle 11g you can.

Let’s start with the CG_REF_CODES, here the simplified version of it:

create table cg_ref_codes
(domain varchar2(20) not null
,low_value varchar2(30) not null
,description varchar2(100)
);

And to place some data in this CG_REF_CODES table, a couple of Domains and some values:

insert into cg_ref_codes values ('YES_NO', 'N', 'No');
insert into cg_ref_codes values ('YES_NO', 'Y', 'Yes');
insert into cg_ref_codes values ('STATUS', '1', 'Open');
insert into cg_ref_codes values ('STATUS', '2', 'Closed');
insert into cg_ref_codes values ('STATUS', '3', 'New');
insert into cg_ref_codes values ('STATUS', '4', 'Old');

The next thing we need is a table that uses come of the Domains of this CG_REF_CODES table.

create table t
(id number primary key
,Active varchar2(1)
,status number
);

This table has an ACTIVE column and a STATUS column, both should be limited by the values defined in the CG_REF_CODES table. Now the Virtual Column functionality comes in handy.

The CG_REF_CODES table gets a Virtual Column for each Domain value:

alter table cg_ref_Codes
add (yes_no_fk as (case when domain = 'YES_NO' then cast (low_value as varchar2(1)) end))
/

alter table cg_ref_Codes
add (status_fk as (case when domain = 'STATUS' then to_number (low_value) end))
/

Note the change of the datatype of the LOW_VALUE column. For the YES_NO domain we “need” the Virtual Column to be a VARCHAR2(1) and for the STATUS domain we “need” a NUMBER datatype.
It is not strictly necessary to “change” the datatype for the YES_NO domain, the default datatype would be the same as the datatype for the LOW_VALUE column. This happens to be a VARCHAR2(30) in this case. Not strictly necessary, but a lot cleaner to do so.

Describing the CG_REF_CODES will look like this, note the datatype of the Virtual Columns

desc cg_ref_codes
Name        Type
----------- -------------
DOMAIN      VARCHAR2(20)
LOW_VALUE   VARCHAR2(30)
DESCRIPTION VARCHAR2(100)
YES_NO_FK   VARCHAR2(1)
STATUS_FK   NUMBER

Before we can place a foreign key on these Virtual Columns, we need a Unique Constraint on these newly defined columns:

alter table cg_ref_Codes
add constraint yes_no_uk unique  (yes_no_fk)
/

alter table cg_ref_codes
add constraint status_fk unique (status_fk)
/

Now we can focus our attention to the T table, and define our foreign keys on the appropriate columns:

alter table t
add constraint t_yesno_fk foreign key (active) references cg_ref_codes (yes_no_fk)
/

alter table t
add constraint t_status_fk foreign key (status) references cg_ref_codes (status_fk)
/

Now it is time to run some simple tests:

insert into t values (1, 'Y', 1);

1 row inserted
insert into t values (2, 'N', 1);

1 row inserted
insert into t values (3, 'Z', 1);

insert into t values (3, 'Z', 1)

ORA-02291: Integriteitsbeperking (ALEX.T_YESNO_FK) is geschonden - bovenliggende sleutel is niet gevonden.
insert into t values (4, 'Y', -1);

insert into t values (4, 'Y', -1)

ORA-02291: Integriteitsbeperking (ALEX.T_STATUS_FK) is geschonden - bovenliggende sleutel is niet gevonden.
insert into t values (5, 'N', -1);

insert into t values (5, 'N', -1)

ORA-02291: Integriteitsbeperking (ALEX.T_STATUS_FK) is geschonden - bovenliggende sleutel is niet gevonden.

The Oracle Exceptions shown above are in Dutch, but you get the gist of it…

Creating an extra entry in the CG_REF_CODES table allows for using additional values in the T table.

insert into cg_ref_codes (domain, low_value, description)
values ('YES_NO', 'Z', 'Something else')
/
insert into t
values (3, 'Z', 1)
/

select *
  from t
/
    ID ACTIVE STATUS
 ----- ------ ------
     1 Y           1
     2 N           1
     3 Z           1

And that’s that. Thank you, Lucas and Nigel for this suggestion.