Referential Integrity with Virtual Columns, the Sequel 20188367001

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.

One Response

  1. Alex Nuijten July 17, 2008