2009-09-01

Only one (alternative 2)

10g friendly approach to store primary currencies. No virtual column used. Structure seems a lot like Tom Kyte suggested. Difference is that all country currencies are here populated in the same table and the materialized view replaced with a foreign key constraint.


create table country(country varchar2(2) constraint country_pk primary key
, primary_currency varchar(3) not null
)
;

create table currency(country references country
, currency varchar(3)
, constraint currency_pk primary key (currency,country)
)
;

alter table country
add constraint primary_curr_in_country_curr
foreign key(primary_currency,country)
references currency(currency,country)
deferrable initially deferred
;


Populating


insert into country (country,primary_currency) values ('US','USS');

insert into currency (country,currency) values ('US','USD');

insert into currency (country,currency) values ('US','USN');

insert into currency (country,currency) values ('US','USS');

commit;

insert into country (country,primary_currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','FIM');

commit;


Changing the default. Easier than before. Less code


update country set primary_currency='USD' where country='US';

commit;

select * from country;
country primary_currency
US USD
FI EUR

select * from currency;
country currency
FI EUR
FI FIM
US USD
US USN
US USS


Testing the model:



update country set primary_currency='USD' where country='FI';

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found

insert into country (country, primary_currency) values ('SW','SEK');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found



There is only one default currency per country, should be obvious.

Be sure to run unindex on your production model.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.