2009-08-27

Only one

Last September Tom Kyte wrote a yet another good article in Oracle magazine The Trouble with Triggers. The point about triggers is very good, but the example and the "Correct Answer" I do not like. He is suggesting to model currencies to two separate tables. In my opinion the data model is entirely wrong if the same thing is modeled in many places. "Less code equals fewer bugs. Look for ways to write less code." Here is my approach to the problem without a materialized view and materialized view logs.

The problem itself is that
-a country must have a default currency
-there is only one default currency per country


create table country(country varchar2(2) constraint country_pk primary key);

create table currency(country references country
, currency varchar(3)
, is_primary varchar2(1) not null
check (is_primary in ('Y','N'))
, primary_country
as (case when is_primary = 'Y' then country end)
virtual
constraint only_one_primary_u unique
, constraint currency_pk primary key (currency,country)
)
;

alter table country
add constraint must_have_at_least_one_primary
foreign key(country)
references currency(primary_country)
deferrable initially deferred;



And that is it. One may see that there is used the "bad hack" behind only_one_primary_u. The unique constraint generates a function-based normal index.
The good thing about this is that constraints are visible in constraint list, not implemented only in a index or a materialized view.


select *
from user_constraints
where constraint_name
in ('ONLY_ONE_PRIMARY_U','MUST_HAVE_AT_LEAST_ONE_PRIMARY');


Populating


insert into country (country) values ('US');

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

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

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

commit;

insert into country (country) values ('FI');

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

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

commit;



Changing the default


update currency set is_primary = 'N' where country = 'US' and currency = 'USS';

update currency set is_primary = 'Y' where country = 'US' and currency = 'USD';

commit;

select * from country;

COUNTRY
US
FI

select * from currency;

COUNTRY CURRENCY IS_PRIMARY PRIMARY_COUNTRY
US USD Y US
US USN N
US USS N
FI EUR Y FI
FI FIM N




A country must have a default currency


insert into country (country) values ('NO');

commit;

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

insert into country (country) values ('SW');

insert into currency (country,currency,is_primary) values ('SW','EUR','N');

commit;

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



There is only one default currency per country


insert into currency (country,currency,is_primary) values ('FI','USD','Y');

ORA-00001: unique constraint (RAFU.ONLY_ONE_PRIMARY_U) violated




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.