Only one (yet again)

Is that not a normal DBA work week.
Installing Oracle on HP-UX cluster
Trying to resolve problematic CPU usage -Solaris
Rewriting loooong query more readable
Fiquring out 9.2 RAC
Talking about a PL/SQL function having comments from the 90's
Talking about modeling tools
Partitioning postgresql
A very good chinese dinner at Dong Bei Hu
Some dark beer
Night in a hotel
Reading awr reports and creating some of my own
Making ssh connections to a 10.2 RAC environment
Installing 11.2
Dumping a datafile block, boy that wait information in v$session mislead me. Should have used snapper. Well the blocks were the same both in primary and standby
Rewriting that loooong query so that it will perform when there will be 10M rows in the future
Sitting in a company meeting and hearing that Solita is recruiting new people


Luckily some actual modeling work also. How to constrain a model where there is a variation of the only one problem. The difference here was that a country does not have to have a default currency. But if it has any currencies there has to be one as a default. And also a restriction that no virtual columns. Oracle version 10.2. In the following example currencies goes to A table and countries to D.

drop  materialized view da_atleast_one_mv;

drop table a cascade constraints purge;

drop table d cascade constraints purge;


create table d(d_id number primary key, current_a_id number)
;

create table a(a_id number primary key
             , d_id references d not null
             , constraint a_u unique(d_id,a_id))
;

alter table d add constraint d_curr_fk foreign key (d_id,current_a_id) references a(d_id,a_id) deferrable initially deferred;


create materialized view log on d with rowid;

create materialized view log on a with rowid;

create materialized view da_atleast_one_mv 
refresh on commit as 
select d.rowid drid, a.rowid arid, d.d_id, a.a_id
  from d, a
 where d.current_a_id(+)=a.a_id
;

alter table da_atleast_one_mv modify d_id constraint da_atleast_one_nn not null;

insert into d values (0,null);

commit;

--now we have a country without any currencies

insert into d values (1,null);

insert into a values (1,1);

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

insert into d values (2,2);

insert into a values (2,2);

commit;

select * from da_atleast_one_mv;

update d set current_a_id=null where d_id = 2;

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")