2009-09-21

Denormalize safely

For some reason there is a need to denormalize values from p table to c table.
How to ensure that denormalized values are the same that original values in p?


drop table c cascade constraints purge;

drop table p cascade constraints purge;

create table p(p_id number(10) primary key
, p_name varchar2(200) not null);

create table c(c_id number(10) primary key
, p_id constraint c_p_fk references p
, c_value varchar2(200) not null);


The denormalization.



alter table c add (p_name varchar2(200) not null);

alter table p add unique (p_name,p_id);

alter table c add constraint c_p_2fk foreign key(p_name,p_id) references p(p_name,p_id);



Make the c_p_2fk deferrable if there is a need to update the denormalized values. To satisfy unindex:

create index c_p_2fk_idx on c(p_id,p_name);

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. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.